Is this too complicated for VBA?

[thefastlife]

I'm trying to automate monthly reports I run.

Here's what I currently do:
1. Grab data from one file
2. Overwrite said data into a "data" tab in a workbook with multiple tabs
3. Each tab is run off a pivot table. Once the new data is in, I refresh and all the tabs' pivots are updated.
4. I export each tab as its own workbook.
5. I hide all rows and columns that do not contain data and merge and center the top column.

These reports get distributed among a bunch of teams, hence the formatting.

I think everything can be done relatively easily except the hiding of rows and columns as that's not necessarily constant.

Any thoughts?

 

[S.H.A.D.O.]

What was the outcome of running the code, did it do what you wanted?

 

[thefastlife]

Sorry that posted before I finished my thoughts.

Thank you very much for that code. Really helpful. There are a few more pieces to this report that include:
1. Opening source data file
2. Copying that data
3. Overwriting old data on an old master file with said new data
4. Refreshing the pivot table across all tabs
5. Exporting each tab as its own workbook
6. Doing the formatting you already helped me with
7. Merging and centering top row

Where would all this code sit? In the master file I presume?

Is this super tedious?

 

[S.H.A.D.O.]

Where would all this code sit? In the master file I presume?

If you mean within the WorkBook then...

1) Press Alt-F11 to open the VBA editor.
2) From the menu, select Insert > Module.
3) On the page that opens, paste the code.
4) Press Alt-Q to close the VBA editor.
5) Press Alt-F8 to open the macro selector.
6) Select HideIt and click Run.

I hope this helps!

 

[thefastlife]

Thanks! One thing with this specific code is that it only updates the worksheet I run it on. I'm sure there's an easy fix for it to work across all worksheets.

I also figured out the code to save each worksheet as its own file.

Only a few more steps to automate!

 

[MARK858]

I'm sure there's an easy fix for it to work across all worksheets.

Sub HideIt2()

    Dim x As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    On Error Resume Next

    For x = 1 To Sheets.Count
        With Sheets(x)
            LastRow = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(what:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(1, Columns.Count)).EntireColumn.Hidden = True
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, 1)).EntireRow.Hidden = True
        End With
    Next x
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

It is also easy to do a search on the forum or a Google search for "vba to loop through all worksheets".