adamclif2000

[adamclif2000]

I need a macro that will copy Column A in Sheet 1 to the next empty column in Sheet 2. I need to keep the same formatting in the destination cells. I need to do this several times and store the data in Sheet 2.

 

[mikerickson]

What have you tried? What code does the Macro Recorder give you?

 

[adamclif2000]

I can only get it to place it in the same destination column, I need it to place the data in the next column so I can keep the data in column A. I need it to move to the next column when I have new data in column a on Sheet 1.

 

[hiker95]

adamclif2000,


Worksheet Sheet2 before the macro:

Excel Workbook
	A	B	C
1
2
3
4
5
6
7
8
9
10
11
12
13
Sheet2

If we start in worksheet Sheet1 with this:

Excel Workbook
	A
1	Title 1
2	A2
3	A3
4	A4
5	A5
6
7
8
9
10
11
12
13
Sheet1

And we run the macro, we get this:

Excel Workbook
	A	B	C
1	Title 1
2	A2
3	A3
4	A4
5	A5
6
7
8
9
10
11
12
13
Sheet2

If we add/change the data in Sheet1:

Excel Workbook
	A
1	Title 1
2	A2
3	A3
4	A4
5	A5
6	A6
7	A7
8	A8
9
10
11
12
13
Sheet1

And run the macro again we get this:

Excel Workbook
	A	B	C
1	Title 1	Title 1
2	A2	A2
3	A3	A3
4	A4	A4
5	A5	A5
6		A6
7		A7
8		A8
9
10
11
12
13
Sheet2

And, add/change the data again:

Excel Workbook
	A
1	Title 1
2	A2
3	A3
4	A4
5	A5
6	A6
7	A7
8	A8
9	A9
10	A10
11	A11
12	A12
13
Sheet1

We get this:

Excel Workbook
	A	B	C
1	Title 1	Title 1	Title 1
2	A2	A2	A2
3	A3	A3	A3
4	A4	A4	A4
5	A5	A5	A5
6		A6	A6
7		A7	A7
8		A8	A8
9			A9
10			A10
11			A11
12			A12
13
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Copy_w1colA_to_w2nextavailablecol()
' hiker95, 09/25/2012
' http://www.mrexcel.com/forum/excel-questions/661331-adamclif2000.html
Dim lr As Long, nc As Long
Application.ScreenUpdating = False
lr = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
nc = Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column + 1
If nc = 2 And Worksheets("Sheet2").Cells(1, 1) = "" Then
  nc = 1
End If
Worksheets("Sheet1").Range("A1:A" & lr).Copy Worksheets("Sheet2").Cells(1, nc)
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Copy_w1colA_to_w2nextavailablecol macro.

 

[adamclif2000]

Thank you so much, works exactly like I need!

 

[hiker95]

adamclif2000,

You are very welcome. Glad I could help.

Thanks for the feedback.

Come back anytime.