VBA to Consolidate Columns into Single Column

[trombin]

Hello all,

I am a beginner when it comes to VBAs, but hoping you all can help me solve this issue I am having. Essentially I have a workbook setup with multiple sheets. One of these sheets "Helper Tab" display all the information I need, but I need to consolidate it all one step further.

I am looking to stack "like" data on-top of each other (i.e A-G, H-N, O-U, and W-AB, whereas A is the same as H, etc.). Here is an example of the "Helper Tab" sheet.

I'd like this data to go into another sheet titled "Output" with a command button that loads the data when I click it (i need it to delete what ever info is currently there when I click it). In my below example I show the data starting on B7, but thats not mission critical. A couple other call outs, the data in the "Helper Tab" sheet is gathered via formulas, so it needs to display the result of the formula (not the formula its self) and it needs to be able to skip blank cells (may have a formula, but will not have any data). Column length may differ slightly, AND may change from week to week (though no column will be expected to go past 1k).
Example of the "Output" sheet.

Thanks in advance for assistance!

 

[alansidman]

Really cannot manipulate data in a picture. You will be better served if you load your data sheets using XL2BB. Look at my signature for more information.

 

[Tom Urtis]

One question, you wrote:
"...it needs to be able to skip blank cells (may have a formula, but will not have any data)"

What do you want to have happen if for example a cell in column D, which as you said contains a formula, returns a null string, but cells A, B, C, E, F, and G of that row show data. Do you want that record of A:G be included or excluded from the transfer?

Your request is otherwise clearly explained, good job with that, and the task is certainly do-able. But, please clarify if you have some event code that the CommandButton triggers when clicked, or whatever code you have behind that button.

 

[trombin]

One question, you wrote:
"...it needs to be able to skip blank cells (may have a formula, but will not have any data)"

What do you want to have happen if for example a cell in column D, which as you said contains a formula, returns a null string, but cells A, B, C, E, F, and G of that row show data. Do you want that record of A:G be included or excluded from the transfer?

Your request is otherwise clearly explained, good job with that, and the task is certainly do-able. But, please clarify if you have some event code that the CommandButton triggers when clicked, or whatever code you have behind that button.

Thanks for the reply! To answer the question. A-G would still be included. The screenshot I provided is a tiny bit misleading as it shows as if the data (I.e site) is in the same position in every column, but that won’t be always be the case. Some weeks will have more sites, so they don’t always line up. I should also clarify the only cells that will be “missing” data, will be some cells at the end of each column. For example A100 might be blank, and if that’s the case you wouldn’t see data in A106+ either.

There is no code currently behind the command button. I put the button on the file and tried my hand at making this work, but came up short. The intended purpose of the button was for me to click it and trigger the data dump from sheet to sheet. In theory I don’t really need a button to do this, just preferred.

 

[Tom Urtis]

Just to be clear, you really want the stacked data to populate columns B:H and not A:G? No big deal either way but just checking.

If I were you, since you are a beginner in VBA as you said, I would delete the ActiveX CommandButton and stick a Form button on the Output sheet instead. It will be an easier task for you to attach the macro I am doing to that Form button when the time comes for that.

 

[Tom Urtis]

I will be signing off so to get you going, this will do what I think you want, with the Output sheet getting the stacked columns into columns A:G.

Sub StackColumns()
Application.ScreenUpdating = False
Dim myConf%
myConf = MsgBox("Do you want to re-stack the columns?", 36, "Please confirm")

If myConf = 7 Then
MsgBox "No problem, just click OK.", 64, "You clicked No."
Exit Sub
End If

Dim lngLastColumnRow&, lngNextStackedRow&, lngColumnCounter&, lngLastColumn&
lngNextStackedRow = 8
lngColumnCounter = 1

Cells.Clear

With Range("A7:G7")
.Value = Array("Start_Date", "Site_Na", "Duration", "Process_Path", "Metric", "Metric_Type", "Value")
.Interior.ColorIndex = 6
End With

With Sheets("Helper Tab")
lngLastColumn = .Range("A1").CurrentRegion.Columns.Count

Do
lngLastColumnRow = .Cells(Rows.Count, lngColumnCounter).End(xlUp).Row
Range(Cells(lngNextStackedRow, 1), Cells(lngNextStackedRow + lngLastColumnRow - 1, 7)).Value = .Range(.Cells(1, lngColumnCounter), .Cells(lngLastColumnRow, lngColumnCounter + 6)).Value
lngNextStackedRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
lngColumnCounter = lngColumnCounter + 7
Loop Until lngColumnCounter > lngLastColumn

Range(Columns(1), Columns(7)).AutoFit
End With

MsgBox "Columns stacked.", , "Done."

Application.ScreenUpdating = True
End Sub

 

[trombin]

I will be signing off so to get you going, this will do what I think you want, with the Output sheet getting the stacked columns into columns A:G.

Sub StackColumns()
Application.ScreenUpdating = False
Dim myConf%
myConf = MsgBox("Do you want to re-stack the columns?", 36, "Please confirm")

If myConf = 7 Then
MsgBox "No problem, just click OK.", 64, "You clicked No."
Exit Sub
End If

Dim lngLastColumnRow&, lngNextStackedRow&, lngColumnCounter&, lngLastColumn&
lngNextStackedRow = 8
lngColumnCounter = 1

Cells.Clear

With Range("A7:G7")
.Value = Array("Start_Date", "Site_Na", "Duration", "Process_Path", "Metric", "Metric_Type", "Value")
.Interior.ColorIndex = 6
End With

With Sheets("Helper Tab")
lngLastColumn = .Range("A1").CurrentRegion.Columns.Count

Do
lngLastColumnRow = .Cells(Rows.Count, lngColumnCounter).End(xlUp).Row
Range(Cells(lngNextStackedRow, 1), Cells(lngNextStackedRow + lngLastColumnRow - 1, 7)).Value = .Range(.Cells(1, lngColumnCounter), .Cells(lngLastColumnRow, lngColumnCounter + 6)).Value
lngNextStackedRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
lngColumnCounter = lngColumnCounter + 7
Loop Until lngColumnCounter > lngLastColumn

Range(Columns(1), Columns(7)).AutoFit
End With

MsgBox "Columns stacked.", , "Done."

Application.ScreenUpdating = True
End Sub

Tom I really appreciate that! It was almost perfect, the only thing that is happening now is it is importing the "header" from the helper tab along with the data.

 

[Tom Urtis]

Yes, you are right, I did miss the fact that the headers are on row 1 of the Helper Tab and I mistakenly included them in the loop.

This should do what you want.

Sub StackColumnsRedux()
Application.ScreenUpdating = False
Dim myConf%
myConf = MsgBox("Do you want to re-stack the columns?", 36, "Please confirm")

If myConf = 7 Then
MsgBox "No problem, just click OK.", 64, "You clicked No."
Exit Sub
End If

Dim lngLastColumnRow&, lngNextStackedRow&, lngColumnCounter&, lngLastColumn&
lngNextStackedRow = 8
lngColumnCounter = 1

Cells.Clear

With Range("A7:G7")
.Value = Array("Start_Date", "Site_Na", "Duration", "Process_Path", "Metric", "Metric_Type", "Value")
.Interior.ColorIndex = 6
End With

With Sheets("Helper Tab")
lngLastColumn = .Range("A1").CurrentRegion.Columns.Count

Do
lngLastColumnRow = .Cells(Rows.Count, lngColumnCounter).End(xlUp).Row
Range(Cells(lngNextStackedRow, 1), Cells(lngNextStackedRow + lngLastColumnRow - 1, 7)).Value = .Range(.Cells(2, lngColumnCounter), .Cells(lngLastColumnRow, lngColumnCounter + 6)).Value
lngNextStackedRow = Cells(Rows.Count, 1).End(xlUp).Row
lngColumnCounter = lngColumnCounter + 7
Loop Until lngColumnCounter > lngLastColumn

Rows(lngNextStackedRow).Delete
Range(Columns(1), Columns(7)).AutoFit
End With

Application.ScreenUpdating = True

MsgBox "Columns stacked.", , "Done."

End Sub

 

[trombin]

Yes, you are right, I did miss the fact that the headers are on row 1 of the Helper Tab and I mistakenly included them in the loop.

This should do what you want.

Sub StackColumnsRedux()
Application.ScreenUpdating = False
Dim myConf%
myConf = MsgBox("Do you want to re-stack the columns?", 36, "Please confirm")

If myConf = 7 Then
MsgBox "No problem, just click OK.", 64, "You clicked No."
Exit Sub
End If

Dim lngLastColumnRow&, lngNextStackedRow&, lngColumnCounter&, lngLastColumn&
lngNextStackedRow = 8
lngColumnCounter = 1

Cells.Clear

With Range("A7:G7")
.Value = Array("Start_Date", "Site_Na", "Duration", "Process_Path", "Metric", "Metric_Type", "Value")
.Interior.ColorIndex = 6
End With

With Sheets("Helper Tab")
lngLastColumn = .Range("A1").CurrentRegion.Columns.Count

Do
lngLastColumnRow = .Cells(Rows.Count, lngColumnCounter).End(xlUp).Row
Range(Cells(lngNextStackedRow, 1), Cells(lngNextStackedRow + lngLastColumnRow - 1, 7)).Value = .Range(.Cells(2, lngColumnCounter), .Cells(lngLastColumnRow, lngColumnCounter + 6)).Value
lngNextStackedRow = Cells(Rows.Count, 1).End(xlUp).Row
lngColumnCounter = lngColumnCounter + 7
Loop Until lngColumnCounter > lngLastColumn

Rows(lngNextStackedRow).Delete
Range(Columns(1), Columns(7)).AutoFit
End With

Application.ScreenUpdating = True

MsgBox "Columns stacked.", , "Done."

End Sub

You sir are incredible! Thanks so much for the help here!