Copy/Paste dynamic range

Question

Starting from Sheet "DATA" range B4:Hx, where x is my last row taking by a row count. I need to copy this range and paste it as values on sheet "bat" starting at A1.

Going forward I need to offset columns in 6. So my second copy will be I4:Ox and so one copying appending into bat sheet. I know where I must stop and I'm informing it using the Funds value.

The first error I'm having is when I try set Column2 = Range("H" & bottomD) value that is giving me "overflow".

And sure I don't know yet if my For loop would work.

Sub Copy_bat()

    Dim bottomD As Integer
    Dim Column1 As Integer
    Dim Column2 As Integer
    Dim i As Integer
    Dim Funds As Integer

        Funds = Sheets("bat").Range("u3").Value

        Sheets("DATA").Activate
        bottomD = Range("A" & Rows.Count).End(xlUp).Row
        Column1 = Range("B4")
        Column2 = Range("H" & bottomD)

        For i = 1 To Funds

          Range(Column1 & ":" & Column2).Copy
          Sheets("Data").Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues, SkipBlanks:=True, Transpose:=False
          Column1 = Colum1.Range.Offset(ColumnOffset:=6)
          Column2 = Colum2.Range.Offset(ColumnOffset:=6)

        Next i

End Sub

What value is under `Range("H" & bottomD)`? Integer values range from `from -2,147,483,648 through 2,147,483,647`. The problem may also be a string instead of a number — AnalystCave.com, Jun 12 '15 at 15:47
since today my "data" sheet have only info till row 9, so I expect this range to be "H9" ...I see stopping mouse over variable "bottomD" that it value "9" — Flib, Jun 12 '15 at 16:09

ZygD · Accepted Answer · 2022-07-27T09:43:41.067

Always use Option Explicit at the beginning of every module to prevent from typos. Always! You had typos at the bottom - Colum1 and Colum2.
Avoid Activate and Select (you had Sheets("DATA").Activate) - better performance, smaller error chance. Instead, you should always explicitly tell VBA which sheet you are referring to.
While pasting values you can simply do something like Range2.value = Range1.value. No need to .Copy and then .Paste.

I did my best to understand what you need. From my understanding you did not use Range data type, while you needed that. This caused you errors.

Option Explicit

Sub Copy_bat()

    Dim bottomD As Integer
    Dim i As Integer
    Dim Funds As Integer
    Dim rngArea As Range

        Funds = Sheets("bat").Range("u3").Value
        
        With Sheets("Data")
            bottomD = .Range("A" & .Rows.Count).End(xlUp).Row
            Set rngArea = Range(.Range("B4"), .Range("H" & bottomD))
        End With
        
        For i = 1 To Funds
          Sheets("bat").Cells(Rows.Count, "A").End(xlUp)(2).Resize(rngArea.Rows.Count, rngArea.Columns.Count).Value = _
            rngArea.Value
          Set rngArea = rngArea.Offset(, 7)
        Next

End Sub

I made one rngArea variable of type Range instead of 2 variables (Column1 and Column2). This code takes info from "Data" sheet and puts that to "bat" sheet. Then offsets to right by 7(!) columns in "Data" sheet and puts data in "bat" sheet below the data that was put previously.

Copy/Paste dynamic range

1 Answers1