CODEWITHSUNDEEP
vbaloopsexcel
Vilgis
Vilgis

Reputation: 36

VBA script to copy a column in the next sheet and paste to the next empty column in the first sheet

I’d need to write a short VBA script for the following situation. The workbook contains multiple sheets. The first sheet is the summary sheet. After the summary sheet there is an irregular number of sheets that contain the information I would like to display on the summary sheet. The information is always in “Column B”. The script should copy “Column B” of each sheet and paste it to the next empty column in the summary sheet. In other words, it should copy “Column B” in “Sheet 2” and paste it to the next empty column in “Sheet 1”, then copy “Column B” in “Sheet 3” and paste again to the next empty column in “Sheet 1”, etc. etc.

All the help is appreciated, thank you!

Upvotes: 0

Views: 925

Answers (2)

Vilgis
Vilgis

Reputation: 36

This worked:

Sub NextWsToNextEmptyColumn()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Columns(2).Copy Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

Next ws

End Sub

Upvotes: 1

user3819867
user3819867

Reputation: 1120

You will need to declare a Worksheet type variable (e.g. Dim ws As Worksheet), to loop by a For Each ws In Activeworkbook.Worksheets ... Next ws. Then you need to define which sheet is the master sheet. I recommend once again a Worksheet, e.g. Set wsMain = ActiveWorkbook.Worksheets("Summary"). To complete it use a 

wsMain.Cells.Find("*",SearchOrder:=xlByColumns,SearchDirection:=xlPrevious).Column

to tell you the index of the last used column in your summary sheet. You can either store it in a variable then increase it by one in your loop or run it in your loop (not resource-efficient but who cares about that two milliseconds).
You can reference columns by index which in your case will be ws.Cells.Columns(2) and wsMain.Cells.Columns(LastColumn + 1).
Have fun writing your code, I hope I could help!

Upvotes: 1

Related Questions