How to set the column where I want to start pasting information

[brunoricardo]

Hello everyone,

The code below aims to copy several sets of columns and past them into another sheet. How can i say that I just want to start the information to be pasted on the column "F"? I think is something to be with the definition of my dimension "s"...

thanks in advance!!

Sub Copy_Over2()
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim s As Integer


Dim Lastrow As Long
Dim Lastrow2 As Long


Sheets("Sheet1").Activate
For i = 3 To 6
s = s + 1
Lastrow = Cells(Rows.Count, i).End(xlUp).Row + 1
Lastrow2 = Sheets("Sheet2").Cells(Rows.Count, s).End(xlUp).Row + 1
For b = 2 To Lastrow
Sheets("Sheet2").Cells(Lastrow2, s).Value = Cells(b, i).Value
Lastrow2 = Lastrow2 + 1
Next
Next
Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

Set s to 5 (Column E) before For i = 3 To 6.

 

[Smitty]

You can set s = 5 before your loop.

Or set s = 6 before your loop, but move the s = s +1 to the end of the loop, since it's generally better to increment your variable after you've used it, not before. It's more straightforward that way.

HTH,

 

[brunoricardo]

Set s to 5 (Column E) before For i = 3 To 6.

Hello! thank you for your help! I already inserted, as you can see below, but there is an error happening. there´s something wrong with syntax used?


Sub Copy_Over2()
Application.ScreenUpdating = False
Dim i As Integer
Dim b As Integer
Dim s As Integer


Dim Lastrow As Long
Dim Lastrow2 As Long


Sheets("Sheet1").Activate

Set s to 5

For i = 3 To 6
s = s + 1
Lastrow = Cells(Rows.Count, i).End(xlUp).Row + 1
Lastrow2 = Sheets("Sheet2").Cells(Rows.Count, s).End(xlUp).Row + 1
For b = 2 To Lastrow
Sheets("Sheet2").Cells(Lastrow2, s).Value = Cells(b, i).Value
Lastrow2 = Lastrow2 + 1
Next
Next
Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

It's just:

s = 5