VBA: Copy-pasting cell value only on worksheets specified by a named range

[Elle Ciel]

Hi all,

I have a named ranged called "Job_Codes" that contains worksheet names on which I want this macro to run. The macro is supposed to copy and paste value the content of R3:R34 into T3:T34 and R27:R101 into T27:T101 on each worksheet listed in "Job_Codes".

The reason I use a named range is because there are other worksheets in the workbook that I do not want to run the macro on, so I use the named range "Job_Codes" as a parameter control.

However, I am getting Run-time error '1004' Select method of Range class failed on this line:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sheets(Cell.Text).Range("R3:R24").Select
</code>
I'm new to VBA and would appreciate any advise. The full code is below.

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub Copy_Last_Update()

For Each Cell In Range("Job_Codes")

Sheets(Cell.Text).Range("R3:R24").Select
Selection.Copy Sheets(Cell.Text).Range("T3:T24").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Sheets(Cell.Text).Range("R27:R101").Select
Application.CutCopyMode = False
Selection.Copy Sheets(Cell.Text).Range("T27:T101").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

Sheets("UserGuide").Select
Range("A1").Select Next

End Sub</code>

 

[database_coder]

First, You need to define Cell. At the top before your For Each statement, put:

 Dim Cell As Variant

Second, just because when you are coding to make your life a lot easier, you should try and put the Next statement on a new line. I believe it should be Next Cell. I will have to confirm this tomorrow when I get into work, and test out your Macro.

database_coder

 

[JoeMo]

You don't need all the selecting - just slows things down. Try this:

Sub Copy_Last_Update()
Application.ScreenUpdating = False
For Each Cell In Range("Job_Codes")
    With Sheets(Cell.Text)
        .Range("R3:R24").Copy
        .Range("T3:T24").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        .Range("R27:R101").Copy
        .Range("T27:T101").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
Next Cell
Sheets("UserGuide").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[Elle Ciel]

Thanks database_coder and JoeMo. I'm learning new things here and there. :D

I tested JoeMo's code and it works perfectly. Thank you very much!

 

[JoeMo]

Thanks database_coder and JoeMo. I'm learning new things here and there. :D

I tested JoeMo's code and it works perfectly. Thank you very much!

You are welcome - thanks for the reply.