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
Option Explicit
at the beginning of every module to prevent from typos. Always! You had typos at the bottom -Colum1
andColum2
.Activate
andSelect
(you hadSheets("DATA").Activate
) - better performance, smaller error chance. Instead, you should always explicitly tell VBA which sheet you are referring to.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.I made one
rngArea
variable of typeRange
instead of 2 variables (Column1
andColumn2
). 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.