copy and paste unto a hidden sheet. 1

[SharonMee]

Hi all:

I want to copy cells of my current worksheet into a sheet9 which I want to hide. I recorded a macro to do the copy and paste. Then I hide the sheet, and tried to run the macro but I am getting an error (probably because sheet9 is now hidden). How do I work around this?

Here is my macro:

Cells.Select
Selection.Copy
Sheet9.Select
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


Any help is appreciated, thanks for your time.


SharonMee

 

[dyarwood]

Make the sheet visible

Sheets("Sheet9").Visible = True

and wrap that part in

Application.ScreenUpdating = False
Application.ScreenUpdating = True
doesn't see anything between these two commands.

Application.ScreenUpdating = False
Cells.Select
Selection.Copy
Sheets("Sheet9").Visible = True
Sheet9.Select
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.ScreenUpdating = True

 

[dyarwood]

Forgot to hide the sheet again

Application.ScreenUpdating = False
Cells.Select
Selection.Copy
Sheets("Sheet9").Visible = True
Sheet9.Select
Cells.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet9").Visible =False

Application.ScreenUpdating = True

 

[SharonMee]

Thanks. What does the Application.ScreenUpdating do?

The reason why I am asking is because the macro I posted is just a portion of the full macro. So, does it matter if I place the Application.ScreenUpdating at the beginning of my full macro and at the end, or do I have to place it exactly when I want to copy and paste in the hidden sheet?

Thanks again

 

[dyarwood]

Application.ScreenUpdating controls whether the screen should update while the macro runs. (If you are selecting sheets and cells you will see this if the updating is set to true but you won't see it if it is set to false.)

Saves the screen flashing away while the macro works.

 

[SharonMee]

Thanks much. That worked perfectly.

SharonMee

 

[macropod]

If you know the source and target cell addresses on both worksheets and you only want to have the same values on both, you could avoid all the selecting, copying, pasting, un/hiding, by simply replicating the values:

Range("Sheet2!A1").Value = Range("Sheet1!A1").Value

Similarly, to replicate formulae (if the source cell has a formula) or values (if the source cell has a value instead of a formula), you could use:

Range("Sheet2!A1").Formula = Range("Sheet1!A1").Formula

Cheers