Macro Question

[Guest]

Is there a way to repeat this macro for columns C thru Z on Sheet2 ?



Sheets("Sheet2").Select

Range("C3:C344").Select

Selection.Copy

Sheets("Sheet3").Select

Range("A2").Select

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste

Thank you in advance.

 

[Guest]

simplest way if you want to do what I think you want to do is change
Range("C3:C344").Select
to
Range("C3:Z344").Select

if you need each column to go to column A individulally to do something else.

for c = 3 to 26
Sheets("Sheet2").Select

Range(cells(3,C),Cells(344,C).Select

Selection.Copy

Sheets("Sheet3").Select

Range("A2").Select

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveSheet.Paste

rest of your action
next C

 

[Don Guillett]

to copy c3:z344 to the last available row from the bottom up on sheet 2. NO
selections

dlr=sheets("sheet3").cells(rows.count,"a").end(xlup).row+1
sheets("sheet2").range(c3:z344").copy sheets("sheet3").cells(dlr,1)

 

[Gord Dibben]

You could tidy this up to get rid of the "selects".

Assuming you want columns C through Z copied and pasted into column A in Sheet3

Sub foo()
For C = 3 To 26
With Sheets("Sheet2")
Range(Cells(3, C), Cells(344, C)).Copy Destination:=Sheets("Sheet3") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End With
Next C
End Sub


Gord Dibben MS Excel MVP