Excel VBA to stack columns in different sheets

Question

I have an Excel workbook with 2 sheets inside. Both sheets have tables that can change in length (row count). I want to be able to have VBA take table1 in sheet1 and align it within table2 on sheet2.

Here's where it gets tricky. The columns do not line up. For example, "Company" in sheet1 might be in column E. However, in sheet2 it's in column V. There are also blanks in the columns. Which is ok as far as data goes, but it's going to make getting to the end of the table's column harder.

I know I'm going to need to list out which columns go where for each column. In sheet1 there are 36 columns. In sheet 2 there are 49. This is perfectly acceptable.

Any guidance would be greatly appreciated. It takes an insane amount of time to do this manually and I'm just trying to speed it up. If it's easier to get everything to compile on sheet3, that's fine.

Here is what I have so far, but it's not liking the stacking part.

Sub Stacker()

'Select Full Report Sheet
Sheets("Full Report").Select

'Find Notes column and copy
Cells.find(What:="Notes", After:=Range("A1"), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows).EntireColumn.Copy

'Select Secondary Report sheet, column E and paste
Sheets("Secondary Report").Select
Range("E1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Sub

Thank you

What do you mean exactly by 'tables' and 'Table1' and 'Table2'? Are these structured (i.e. ListObject) tables? — user4039065
– user4039065, Commented Jun 29, 2018 at 20:25
Are all of the Table 1 columns present in Table 2? Are you copying all columns from Table 1? — Tim Williams
– Tim Williams, Commented Jun 29, 2018 at 23:27
What do mean by "stack" and "to have VBA take table1 in sheet1 and align it within table2 on sheet2"? Are you trying to get both tables to appear on the same page, with all shared columns lined up one above the next? Or, are you just trying to reorder the columns where they are and leave each table on its own sheet or ... something else? — SeanW333
– SeanW333, Commented Jun 30, 2018 at 0:54
You may be able to use powerquery and append the tables. As the columns do not vary you can do the column re-ordering on one of the tables so the line up (not sure if powerquery even required you to do this if they have matching headers - so try without first) You may need to add dummy columns to the shorter width table. Again, powerquery may handle this for you or you can use its column add feature. — QHarr
– QHarr, Commented Jun 30, 2018 at 3:35
@Friendly_Neighborhood_BA How 'bout you be as friendly as your pseudonym and answer the comments your question has generated, and/or mark answers as helpful/solved if that be the case. — jeffreyweir
– jeffreyweir, Commented Jul 6, 2018 at 7:49

jeffreyweir · Accepted Answer · 2018-06-30 16:10:12Z

0

I've previously written code that lets you mash all your data from seperate tables into one table, no matter the order of the columns. See https://stackoverflow.com/a/47279374/2507160

I'd also suggest trying out PowerQuery to do the same. See https://stackoverflow.com/a/47170312/2507160

answered Jun 30, 2018 at 16:10

jeffreyweir

4,8841 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA to stack columns in different sheets

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related