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

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
 
J

JLGWhiz

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

paste range from one sheet to each sheet after 3
set print area on multiple sheets 4
copy format to new sheet 2
how do i loop thru all the worksheets in 2 files one by one 0
Private Sub Workbook_newsheet 5
Set Active Cell to A1 on Open Q 3
run macro on all sheets in workbook after specific sheet 1
for each sheet in workbook 2

Top