Error on sh.Paste sh.Range = ("A1")

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

First one works (Macro 2000)
Second one (same code) doesn't. Error on
Sh.Paste sh.Range = ("A1").


Sub Macro2000()
'
Dim sh As Worksheet
'copy range from template sheet
Sheets("Ind Templates").Rows("38:50").Copy
'for each sheet in workbook after "ind templates"
x = Sheets("Ind Templates").Index
For Each sh In ThisWorkbook.Sheets
If sh.Index > x Then
'paste the copied data 1row below the last row used
sh.Paste sh.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next
Application.CutCopyMode = False
End Sub

Sub Macro1000()
Dim sh As Worksheet
'copy range from template sheet
Sheets("Ind Templates").Rows("1:15").Copy
'for each sheet in workbook after "ind templates"
x = Sheets("Ind Templates").Index
For Each sh In ThisWorkbook.Sheets
If sh.Index > x Then
'paste the copied data 1row below the last row used
sh.Paste sh.Range = ("A1")
End If
Next
Application.CutCopyMode = False
End Sub
 
Hi Jeremy,

You can use:
For i = x To Sheets.Count
Sheets("Ind Templates").Rows("1:15").Copy Sheets(i).Range("A1")
Next

This is bad syntax:

sh.Paste sh.Range = ("A1"

But even if it was arranged as sh.Paste sh.Range("A1")

It would not work because it is not on the same line as the Copy command.
You would have to use PasteSpecial with one of the Paste:=xl constants to
access the clipboard and make it paste.

I still goof this up once in a while. Look over the examples in the help
files and read the remarks. After a while it starts to make sense.
 
Back
Top