simple macro.....

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
hi,
i have two sheets....1 and 2
in sheet1, i have data in E3:BX300......

what i need to do is column by column copy the columns in sheet1 and place them in sheet2 starting in cell E3.

So far, i ihave this code....however, i don't know how to change the column in sheet1 for each i

Thanks for any help in advance.





Code:
Sub copy()
Dim i As Integer

For i = 1 To 71

    Sheets("sheet1").Select
    Range("E3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.copy
    Sheets("sheet2").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Why do you need a loop?
Code:
Sheets("Sheet1").Range("E3").CurrentRegion.Copy
Sheets("Sheet2").Range("E3").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
This should work...
Code:
Sub copy() 
Dim i As Integer 

For i = 5 To 76 
    LR = Sheets("sheet1").Cells(Rows.Count, i).End(xlup).Row
    Sheets("sheet1").Range(Cells(3, i), Cells(LR,i)).Copy 
    Sheets("sheet2").Cells(3, i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
Next i
End Sub

Actually, if all colums have the same amount of Rows, you don't even need to loop...

Code:
Sub copy() 
Dim i As Integer 
    LR = Sheets("sheet1").Cells(Rows.Count, "E").End(xlup).Row
    Sheets("sheet1").Range("E3:BX" & LR).Copy 
    Sheets("sheet2").Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
End Sub
 
Upvote 0
because my data in sheet1 is in E3:BX300 which is 71 columns and i want the values from each column in sheet1 to show up in the same column (E) in sheet2.

so at time 1, column E of sheet1 would copy over to E in sheet2.
at time 2, column F of sheet1 would copy over to E in sheet2, and so on.
 
Upvote 0
Well the code I posted should do that.

And so should jonmo's too.

Unless we're missing something.:eek:
 
Upvote 0
oh really?
wow!

how does your code know how to go to the next column?

Sheets("Sheet1").Range("E3").CurrentRegion.Copy
Sheets("Sheet2").Range("E3").PasteSpecial Paste:=xlPasteValues
 
Upvote 0
Well it doesn't really.:)

It's just like highlighting a whole range and copying that.

Note my code might not actually work for you as it relies on a couple of things.

1 The columns are contiguous.

2 They stand alone. ie there's no data in column D or column BY

If these assumptions are wrong your probably better off with jonmo's code.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top