Excel VBA to stack columns in different sheets

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

2 Answers
2

This looks like a job for a Super Dictionary!!!

Function DictionaryStacker()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim dic As Object
Dim cell As Range
Set dic = CreateObject("Scripting.Dictionary")

With Sheets("Secondary Report")
For Each cell In .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))
'Add a reference to the first empty cell in the column
Set dic(cell.Value) = cell(.Rows.Count).End(xlUp).Offset(1)
Next
End With

With Sheets("Full Report")
For Each cell In .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))
If dic.Exists(cell.Value) Then
'Reference data in column starting at Row 2
With .Range(cell.Offset(1), cell(.Rows.Count).End(xlUp))
'Append the referenced Range on Full Reports to the Range stored in the Dictionary
dic(cell.Value).Resize(.Rows.Count).Value = .Value
End With
End If
Next
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Function


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

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.