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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Why do you need a loop?
Code:
Sheets("Sheet1").Range("E3").CurrentRegion.Copy
Sheets("Sheet2").Range("E3").PasteSpecial Paste:=xlPasteValues
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Well the code I posted should do that.

And so should jonmo's too.

Unless we're missing something.:eek:
 

sssb2000

Well-known Member
Joined
Aug 17, 2004
Messages
1,169
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,102
Messages
5,928,065
Members
436,586
Latest member
latintxn

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
Top