macro paste rng to Al Sheet's next empty cel

August 14, 2011 at 00:52:39
Specs: Windows 7
I'm new to the coding
I need a macro which copy a definite range, say Sheet ("Footer").range ("A1 : H10")
I need to paste the above range to all sheets last row.
I have all sheet's name in one sheet called ("cust").Range("A")

I have the below code but it stuck with the line

Lastrow = Sheets("wks").Cells(Rows.Count, "A").End(xlUp).Row

Any help will be appreciated

Public Sub Footer()

Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
Lastrow = Sheets("wks").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("wks").Range("A" & Lastrow + 1).PasteSpecial
Application.CutCopyMode = False
Next wks

End Sub


See More: macro paste rng to Al Sheets next empty cel

August 14, 2011 at 14:44:09
When you put quotation marks around a string in VBA, it becomes a text string.

Therefore, when VBA sees Sheets("wks") it is looking for a Sheet named "wks".

Try this instead:


You could also loop through the sheets by number:

For shtNum = 1 to Sheets.Count

To avoid pasting the range into the sheet named Footer, add an If-Then:

For shtNum = 1 To Sheets.Count
 If Not Sheets(shtNum).Name = "Footer" Then
 End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question