VBA continuously updating range's column number

Question

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

Vityata · Answer 1 · 2018-06-26T14:09:21.333

3

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

edited Jun 26 '18 at 14:09

answered Jun 26 '18 at 13:49

Vityata

42,633
8
55
100

@barskyn - but I guess the error is somewhere else now? – Vityata Jun 26 '18 at 13:51
I am getting an error message for that specific line – barskyn Jun 26 '18 at 13:52
@barskyn - then write `Dim rr as String` on the line before. – Vityata Jun 26 '18 at 13:52
1

Unfortunately, I'm still receiving an error message, but not it for the line `worksheets("scrap").Range(rr).Paste` – barskyn Jun 26 '18 at 13:54
@barskyn - try adding Option Explicit on the top of the code and compile it. – Vityata Jun 26 '18 at 13:55
@barskyn - new error message is super. Which line and what message? – Vityata Jun 26 '18 at 13:56
The top of my code looks as follow `Sub Macro1() Option Explicit Dim ws As Worksheets` but the now I am getting an error message for the very first line `Sub Macro1()` – barskyn Jun 26 '18 at 13:59
@barskyn - the option explicit should be on the top of the module, not inside the Sub. See the link from my answer. – Vityata Jun 26 '18 at 14:00
1

@barskyn - `Dim ws As Worksheets` is **wrong**. Worksheet**s** is plural and a object collection. You do not want that. You want `Dim ws As Worksheet` instead which is a singular object and **not** a collection. – Jun 26 '18 at 14:25

score 0 · Answer 2 · 2018-06-26T14:26:38.523

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.

Thanks for the catch! I thought I had them all but definitely missed that one. — , Jun 26 '18 at 14:27

VBA continuously updating range's column number

2 Answers2