Consolidating data using VBA, end with set Layout

[JaapHeijndemans]

Hello,

Im working on a large file with around 22 tabs and I want to use VBA to consolidate all this data in a master file. I have the following code that I am using right now on my test file that is doing a perfect job of consolidating all the information:

Sub CombineWorksheets()
'Declare the variables
Dim wksCombined As Worksheet
Dim wks As Worksheet
Dim CalcMode As Long
Dim LastRow As Long

'Change the settings for Calculation and ScreenUpdating
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the Total Overview WorkSheet if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Sheets("Total Overview").Delete
Application.DisplayAlerts = True

'Add a new worksheet before the first worksheet in the active workbook
Set wksCombined = Worksheets.Add

'Name the new worksheet
wksCombined.Name = "Total Overview"

'Set up the headers for the new worksheet
With Worksheets(3)
.Range("A1:V1").Copy Destination:=wksCombined.Range("A1")
End With

'Loop through each worksheet within the active workbook
For Each wks In ActiveWorkbook.Worksheets
'Skip the new worksheet
If wks.Name <> "Total Overview" Then
With wks
'Find the last used row in Column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'Copy the data from the current worksheet to the first available row of the new worksheet starting at Column A
.Range("A2", .Cells(LastRow, "V")).Copy Destination:=wksCombined.Cells(wksCombined.Cells.Rows.Count, "A").End(xlUp).Offset(1)
End With
End If
Next wks

'Restore the settings for Calculation and ScreenUpdating
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

'Display a message indicating that the macro has finished
MsgBox "Completed...", vbInformation

End Sub


The problem that I run into is that I need to update this quite often and everytime I run the script the layout resets. My questions is if its possible to run a line in the script, that will allow me to change the layout of the final page without needing to doing it manually after I run the script.

Anyone that could help me?

 

[wideboydixon]

What changes do you make to the layout? It it just column sizing or something else?

WBD

 

[PatOBrien198]

Can you record a macro of you doing the layout changes, then add a line at the bottom of your script to run the macro that you just recorded?

 

[JaapHeijndemans]

Oh god, thank you for the response Pat, I totally forgot about this.
I recorded the macros and after some adjusting I ran the script and it works perfectly.

Thanks at least for opening my eyes for this option hehe.