Reputation: 383
I want to copy the data from each worksheet and then copy it into new a worksheet called scrap, I want to adjust where the selection is pasted for each worksheet and I am trying to do this in the rr variable, but I am getting an error. How can I continuously change the the row number as I iterate through?
Sub Macro1()
Dim ws As Worksheets
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet
ws_num = ThisWorkbook.Worksheets.Count - 2
For I = 1 To ws_num
e = 9
s = 39
ThisWorkbook.Worksheets(I).Activate
Range("A9:J39").Select
Selection.Copy
rr = "A" + CStr(s) + ":" + "J" + CStr(e)
Worksheets("scrap").Range(rr).Paste
e = e + 30
s = s + 30
Next
End Sub
Upvotes: 0
Views: 70
Reputation:
You only require the top-left cell for the destination of a paste operation and a single destination is actually a parameter of the range.Copy operation.
Worksheets is a collection, you want Worksheet (singular)
Sub Macro1()
Dim starting_ws As Worksheet
dim rr as string, I as long, e as long, ws_num as long
'I have no idea what you want to do with this after initializing it
Set starting_ws = ActiveSheet
ws_num = ThisWorkbook.Worksheets.Count - 2
e = 9
For I = 1 To ws_num
rr = "A" & CStr(e)
with ThisWorkbook.Worksheets(I)
.Range("A9:J39").Copy _
destination:=Worksheets("scrap").Range(rr)
end with
e = e + 31 'there are 31 rows in A9:J39
Next i
End Sub
Declare your variables. Go into the VBE's Tools, Options and put a check beside Require variable declaration.
Upvotes: 0
Reputation: 43595
String concatenation operator in VBA is &
, not +
. Thus, try:
rr = "A" & CStr(s) & ":" & "J" & CStr(e)
Here are two points to improve it further:
Avoid Select and Activate, when you are working with VBA: How to avoid using Select in Excel VBA;
Write Option Explicit
on the top of the module and make sure that you declare every variable explicitly. - Option Explicit MSDN
In general, this should do the needed:
Sub TestMe()
Dim counter As Long
Dim wsTotal As Long
wsTotal = ThisWorkbook.Worksheets.Count - 2
Dim starting As Long: starting = 9
Dim ending As Long: ending = 39
Dim ws As Worksheet
Dim rangeToCopy As Range
For counter = 1 To wsTotal
Set ws = ThisWorkbook.Worksheets(counter)
With ws
Set rangeToCopy = .Range(.Cells(starting, "A"), .Cells(ending, "J"))
rangeToCopy.Copy ThisWorkbook.Worksheets("scrap").Range(rangeToCopy.Address)
End With
starting = starting + 30
ending = ending + 30
Next counter
End Sub
Upvotes: 3