month end variance analysis

[sschwant]

Hello -

I've got a client I'm assisting with automating a month end var analysis process (Budget vs. Actual expenses).

We've boiled it down to an Excel exercise ...

but it involves lots of steps of staging raw data from different sources so that a pivot table can be refreshed with a calculated field for the variances. ....

However, the VB code I've created using the macro recorder does not allow for a repeatable process month / month as the data sets and range references will vary month / month.

Someone suggested setting up data sets as Tables (formatted) as such ... but not sure that's the best path to resolution ....

Thoughts ??

Thanks,

Steve


Here's on sample of some of the code:

Sheets("Actuals Consol").Select
Range("A2").Select
Selection.End(xlDown).Select
Range("B4731").Select
Selection.End(xlUp).Select
Range("A4403").Select


basically the staging of data involves lots of copy/paste/values and re arranging of columns and the above is just one example of how the code would break down month/month as the references would no longer be valid ...

Pls help ...

Thanks!!!

Steve

[SamT]

Please post a sanitized version of the Workbooks.

With truncated Data Sets please. We just need to see the data structure itself with just enough data for code testing.


Someone suggested setting up data sets as Tables (formatted) as such ... but not sure that's the best path to resolution ....
It almost always is.

[sschwant]

can I upload an Excel file ?

[mdmackillop]

Use Go Advanced/Manage Attachments. I'll move this to the Excel Forum

[sschwant]

I'm going to try and clean up my file a bit and then will upload .... thanks

[sschwant]

my work book is ~ 50 MB. I zipped it up and it reduced to ~ 5 MB, but I am unable to upload the compressed file ...

Is there a file size constraint ?

[sschwant]

I've uploaded my work book unzipped to Dropbox.com:

[SamT]

IIRC, the Limit is 1MB.


With truncated Data Sets please. We just need to see the data structure itself with just enough data for code testing.
That would be 3 Rows of data, unless the code requires more than 3 unique value. Only one Sheet of each type of unique Header Rows.

See Also:
https://support.office.com/en-us/article/Clean-excess-cell-formatting-on-a-worksheet-E744C248-6925-4E77-9D49-4874F7474738

http://www.vbaexpress.com/kb/getarticle.php?kb_id=83

[sschwant]

https://www.dropbox.com/sh/mbtgsi7zqvndkpj/AAAkGec_dB8a3oRZ2ZEeR_eUa?dl=0

[SamT]

Sorry, sschwant, I pay for internet per used bandwidth.

[sschwant]

I'll be offline for a bit but now w/ the raw data attached to this thread and the link to the workbook, I'm hoping someone can please take a look and resolve this challenge of differing ranges the raw data presents itself in month / month ... as is the code will break
thanks!

[SamT]

From Paul Hossler via PM:

1. Open the XLSM as zip and look in\xl

calcChain =2MB

Styles = 6.1 MB . In Styles.xml\cellStyles.count = 37247

sheet4 = 106MB

sheet3 = 26.7 MB


2. Just deleting rows and columns past data reduced file size from 19.7 MB to 5.1 MB (Sheet4 now 1.9MB)

3. This macro takes the #styles from 37247 to 123 and file size to 4.9MB, but ....


Option Explicit
Sub DeleteStyles()
Dim i As Long

MsgBox ActiveWorkbook.Styles.Count

On Error GoTo BadName
For i = ActiveWorkbook.Styles.Count To 1 Step -1
If ActiveWorkbook.Styles(i).BuiltIn Then
Debug.Print "Keeping # " & I
Else
Debug.Print "Deleting # " & I
ActiveWorkbook.Styles(i).Delete
End If
If i Mod 100 = 0 Then DoEvents

Next I

MsgBox ActiveWorkbook.Styles.Count

Exit Sub

BadName:
MsgBox "Cannot delete '" & ActiveWorkbook.Styles(i).Name & "'"
Resume Next
End Sub



... there's 76 styles that have illegal style names (looks like they start with a space) and can't be deleted from VBA or the worksheet

Those I deleted manually by editing xl\styles.xml with XMLnotepad


So other than giving Hossler a challenge, what was this for? :-) The file is still too large to post Paul

[SamT]

Paul managed to take your file from 50Mb,zipped,) to 5MB by deleting unused stuff. If you remove masses of rows of data we don't need here, you can get it under 500Kb easy.

Note that when he manually performed an ExcelDiet, http://www.vbaexpress.com/kb/getarticle.php?kb_id=83, He removed 104MB, (unzipped,) of invisible unused junk just from sheet4.

[mdmackillop]

Sort JE tbl by Desc. - - cut and paste AP Accruals into new tab


Do you start without an AP Accls tab?


Where is an AP AccruaL found? Do you mean Filter?


Return to JE tab, delete rows where AP Accruals were


Filter on Desc., and search for "Xfers"


IF IC Transfers exist, follow steps, 5-6 above: cut/paste to new tab (confirm they net to zero) -- IF macro gets created add Msg Box w/ warning if not footing to zero


What is new sheet called?


Create a tab for consolidation of AP Qry data set and JE data set, set tab name to "Actuals Consol"


Do you start without an Actual Consols tab?


Grab Grp ID, Dept, Vendor Name and Amt from Qry Ap - and copy into Actuals Consol tab


(re-arrange columns in both tabs as 1) Grp ID, 2) Dept, 3) Division, 4) Vendor name and 5) Amt using Cut Col & Insert Copied Col . . . Insert blank col between Dept and Vendor Nm


Leave Div column blank for now


Repeat steps 10-12 for JE details (grab Grp ID, Dept, Vendor Name and Amt from JE Detls - and append into Actuals Consol tab) and append to Consol


Pull down FC details from master file on network for current period. Include columns or fields: GrpID, Dept, Division, Vendor and Fcst Amt


Should this not be done before you run the macros?


Run Vlookup on Grp ID in Actuals to pull in Div name from Fcst file. Actuals data set should now be completely staged & ready to consol w/ Fcst.


Merge, append, consolidate Actuals and FC datasets onto "Actuals and FC Consol" tab.


Build PT: Pull Div, Dept and Grp ID into Rows, Pull FC amt and Act amt into Values box and change from count to sum. Set Vendor as Filter.


Pull in Vlookup f(x) to pull in Vendor name by Grp ID

[sschwant]

Sorry about the file size .... I was able to get it down to under 20 MB. And, then zipped it down to under 9 MB.

Both are available on dropbox.com:

https://www.dropbox.com/s/pxf1o3o5dz2ov36/Month%20End%20Steps%20to%20build%20PT%20-%20Clean.zip?dl=0

Here is an over view of the process (not sure what I have documented on the first tab is still relevant).


Step 1: manually grab the raw data as 3 sets of data: AP query and JE table (both from MS Access) and current period Forecast data from an Excel file. Copy/paste/values onto "AP Qry", "Stage raw JE" and "FC Details" tabs. Assumption here is that those sheets have been cleared of all previous records (Delete macro buttons).

Step 2: run the Stage AP data macro: Sub Stage_AP_data_step_1()

Step 3: run the Stage JE data macro: Sub Stage_JE_raw_Data_Step2()

Step 4: run the Purge JE data which removes any AP Accruals and "Xfers" (in this period, there were none)

Step 5: copy the data from the Stage JE data into the "tbleJE" tab which has ~ 15 formulas to the right that applies rules to assign a Group ID

Step 6: copy the data from the AP Qry tab and the tblJE tab and merge these two sets onto the Actuals Consol tab.

Step 7: merge the data from Actuals Consol and FC Details onto the consolidated FC and Actuals Consol tab

Step 8: refresh the Pivot Table

Final step would be to export the Actuals and FC Consol and PT tabs as Create Copy to new Workbook, and then Delete all the data so the work book is empty and ready for the next month's iteration ...

[Paul_Hossler]

@sschwant -- I can PM you a GoogleDrive link with the file SamT was referring to if you still need it

In general, my experience is when there is a lot of copy/pasting happening from different workbooks, Excel creates styles like crazy. When there are more 32K+, it gets a little sticky. Somehow there were 76 style names that started with a space, and couldn't be VBA deleted for some reason


16380


There's a lot of 'dead' formulas. If you're going to automate the process, add them at the end and only where needed.
16382


These go to row 5000 with more than a dozen useless/unneeded formulas. If you really need the data there, a macro to do the work of the formual and which puts the result values into the ws and only for the cells that are needed would be faster and smaller wb


16385



Maybe re-think the PT's -- Do you need a complicated Vendor formula with 4-5 VLookups? Vendor is already a Page Field


16381


An XLSX and XLSM is really just a zip file. Opening with a zip program, you can see possible trouble spots. Like AP Qry Data set with only 10604 cells, should not be 101 MB

1638316384


I like to separate the 1) data from the 2) processing from the 3) reporting from the 4)analysis.

1) So just worksheets with dynamic data (e.g. monthly extract) and static data (vendor lists), with a load/clean macro if needed
2) Processing / cross referencing / Vlooking-up from static data tables
3) Detailed list(s) (data dump type sheets)
4) Generating PT's / charts / etc.

Whenever possible, I prefer to add a new WS instead of re-reusing one since that how a WS can get corrupted, and copy/paste-values to the data sheet and then applying formats

[sschwant]

I've boiled my work book down to a 3 step process. 1) Delete/Clear out all data (via macro), 2) copy/paste/values for 3 data sets, 3) run the stage data master macro . . . this works fine for April, but bombs out for May. Will have to get off VPN in order to upload attachments ... but here is the code snippet for Staging the data:



Sub Stage_All_Data_and_Refresh_Pivot()
Call Stage_AP_data_step_1
Call Stage_JE_raw_Data_Step2
Sheets("PT").Select
Range("A5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
MsgBox "Done"
End Sub


Sub Stage_AP_data_step_1()
'
' AP_data Macro
' After running Qry_Mrg in NCT database, copy paste values into the "AP Qry Dataset" tab onto cell A1.
' This section rearranges the columns prior to merging AP & JE data on "Actuals Consol" tab.


Application.ScreenUpdating = False
Sheets("AP Qry Dataset").Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Cells.Select
Selection.Font.Size = 10
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A2").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("E:E").Select
Selection.Cut
Range("R1").Select
ActiveSheet.Paste
Range("S1").Select
ActiveCell.FormulaR1C1 = "Div"
Columns("N:O").Select
Selection.Cut
Range("T1").Select
Selection.Insert Shift:=xlToRight
Range("O1:S1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With

End Sub


Sub Stage_JE_raw_Data_Step2()
'


Application.ScreenUpdating = False
Sheets("Stage raw JE data").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Cells.Select
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Rows("1:1").Select
Selection.RowHeight = 24.75
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.Zoom = 90
' Stage_JE_raw_data Macro - part 2
' Rearrange columns on worksheet after copying and pasting values onto blank sheet "Stage raw JE data"


Sheets("Stage raw JE data").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E1").Select
ActiveCell.FormulaR1C1 = "GroupID"
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1").Select
ActiveCell.FormulaR1C1 = "Division"
Range("P1").Select
ActiveCell.FormulaR1C1 = "VendorName"
Columns("P:Q").Select
Selection.Cut
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("H1").Select
Cells.EntireColumn.AutoFit
Range("A2").Select

' Calls next macro - "purge" AP Accruals and Xfers
Call Purge_JE_Data
End Sub




Sub Purge_JE_Data()
'
' Purge_JE_Data Macro


' This section moves and then deletes rows withAP Accruals in the Desc. field to the AP Accruals tab


Application.ScreenUpdating = False
Sheets("Stage raw JE data").Select
Cells.Select
ActiveWorkbook.Worksheets("Stage raw JE data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stage raw JE data").Sort.SortFields.Add Key:=Range _
("Q2:Q10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Stage raw JE data").Sort
.SetRange Range("A1:AV10000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("Q1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$R$10000").AutoFilter Field:=17, Criteria1:= _
"=AP Accruals", Operator:=xlAnd
Range("A2:R2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut

Sheets("AP Accls").Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select

' This section does the same for "IC Transfers or "Xfers""

Sheets("Stage raw JE data").Select
Rows("512:513").Select
Range("A513").Activate
Range(Selection, Selection.End(xlUp)).Select
Rows("2:513").Select
Range("A513").Activate
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$R$10000").AutoFilter Field:=17, Criteria1:= _
"=*Xfers*", Operator:=xlAnd
Rows("3620:3620").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Sheets("IC Transfers").Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Stage raw JE data").Select
Selection.AutoFilter
Range("A2").Select
Call Copy_Stgd_JE_data_to_TblJE
End Sub
Sub Copy_Stgd_JE_data_to_TblJE()
'
Application.ScreenUpdating = False
Sheets("Stage raw JE data").Select
Range("A2:R2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

' Switch sheets
Sheets("tblJE").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Insert_Group_ID_in_tblJE Macro


Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[31]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E3619")
Range("E2:E3619").Select
Range("A1").Select
Call Copy_AP_Data_Onto_Acutals_Consol_tab
End Sub
Sub Copy_AP_Data_Onto_Acutals_Consol_tab()


' Copies staged AP Qry data, then calls Copy JE macro to copy from TblJE


Application.ScreenUpdating = False
Sheets("AP Qry Dataset").Select
Range("O2:S2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Actuals Consol").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Call new_copy_tbl_JE

End Sub
Sub new_copy_tbl_JE()


Sheets("tblJE").Select
Range("E2:I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Actuals Consol").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3").Select
Selection.End(xlDown).Select
Range("A753").Select
ActiveWindow.SmallScroll Down:=12
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("E747").Select
Call Copy_FC_to_Consol
End Sub




Sub Copy_FC_to_Consol()
'
' Copy_FC_to_Consol Macro
'


'
Application.ScreenUpdating = False
Sheets("FC Details").Select
Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Actuals and FC Consol").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("FC Details").Select
ActiveWindow.SmallScroll Down:=-15
Range("D2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveSheet.Next.Select
Range("D2").Select
ActiveSheet.Paste
Range("C2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C2:C4166")
Range("C2:C4166").Select
Range("A2").Select
Call Copy_Actuals_Consol_to_Actuals_FC_Consol
End Sub
Sub Copy_Actuals_Consol_to_Actuals_FC_Consol()
'
' Copy_Actuals_Consol_to_Actuals_FC_Consol Macro
'


Sheets("Actuals Consol").Select
Range("A2:F2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Actuals and FC Consol").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3").Select
Selection.End(xlDown).Select
Range("A4167").Select
ActiveWindow.SmallScroll Down:=12
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4166").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C4166:C8896")
Range("C4166:C8896").Select
Columns("E:F").Select
Selection.Style = "Comma"
Range("A2").Select
End Sub

[sschwant]

main template zipped down to 3.2 megs, and Apr/May raw data file is 1.6 megs.

https://www.dropbox.com/s/wdarhg4177hpe78/April%20and%20May%20raw%20data.xlsx?dl=0

https://www.dropbox.com/s/vmgngj2qx9rnbmh/Month%20End%20Steps%20to%20build%20PT%20-%20Clean%20for%20April%20vs%202.zip?dl=0

Per previous posts ... macros work fine for April, but bomb out when updating w/ May data ...

Thanks,

Steve

[sschwant]

1. Open the XLSM as zip and look in\xl

calcChain =2MB

Styles = 6.1 MB . In Styles.xml\cellStyles.count = 37247

sheet4 = 106MB

sheet3 = 26.7 MB

Not sure what you mean by open as zip and /xl ????

Thanks,

Steve

[sschwant]

Exploring the possibility of staging data as formatted tables, Table1, Table2, etc. But same problem arises, e.g., My QryAP data set for May has 1095 rows. My QryAP data set for Apr only has 751. If going from April to May and I have Table1 set for this Qry data set, I can just hit GoTo Table1 and hit delete, copy in the new longer set of rows of data for May and boom Table1 now has 1095 rows and the references for the range of data now reflect the additional rows ..... However, what if was the reverse .... say Apr had 1095 rows but May only has 751. GoTo Table1, Delete data, cpy/paste in new values .... now the range for Table1 is still set to bottom row of 1095 - even though there are only 751 rows of actual data. Now, all the rows between 752 - 1095 are blanks.

[Paul_Hossler]

Not sure what you mean by open as zip and /xl ????

Thanks,

Steve

The easiest way for me is to rename SomeThing.xlsm as SomeThing.xlsm.zip

and open the file.

It looks like the pictures in #16, and /xl is one of the folders

I just like to do this to investigate possible issues, like "WHY is my file SO big?"

[Paul_Hossler]

Exploring the possibility of staging data as formatted tables, Table1, Table2, etc. But same problem arises, e.g., My QryAP data set for May has 1095 rows. My QryAP data set for Apr only has 751. If going from April to May and I have Table1 set for this Qry data set, I can just hit GoTo Table1 and hit delete, copy in the new longer set of rows of data for May and boom Table1 now has 1095 rows and the references for the range of data now reflect the additional rows ..... However, what if was the reverse .... say Apr had 1095 rows but May only has 751. GoTo Table1, Delete data, cpy/paste in new values .... now the range for Table1 is still set to bottom row of 1095 - even though there are only 751 rows of actual data. Now, all the rows between 752 - 1095 are blanks.


Don't hard code data ranges like Range("Q2:Q10000"), but

1. use Range("A1").CurrentRegion to capture just the cells on all sides of A1 (this means you can't use totally empty columns as spacers)

2. use Range("A1").End(xlDown) to get the first non-blank cell in column A going down (might not be the last data in col A

3. use Range("A1").End(xlDown).Offset(1,0) to get the next cell after the last non-blank cell in column A

4. use Activesheet.Cells(Activesheet.Rows.Count,1).End(xlUp) to get the last non-blank cell in column A

I think it's all that copy pasting that created all the different styles with the same formatting (picture in #16)



Sub Purge_JE_Data()
'
' Purge_JE_Data Macro
' This section moves and then deletes rows withAP Accruals in the Desc. field to the AP Accruals tab
Application.ScreenUpdating = False
Sheets("Stage raw JE data").Select
Cells.Select
ActiveWorkbook.Worksheets("Stage raw JE data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stage raw JE data").Sort.SortFields.Add Key:=Range _
("Q2:Q10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Stage raw JE data").Sort
.SetRange Range("A1:AV10000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With




Would become something like (not tested, not even syntax-checked), but processing is based on the data content at the time.

Say data is in A1:Z1234.

rData = Range("A1:Z1234") and rDataNoHeaders = Range("A2:Z1234") and .SortFields.Add Key:=rDataNoHeader.Columns(17) is Range("Q2:Q1234")



Sub Purge_JE_Data()

Dim rData as Range, rDataNoHeaders as Range

Application.ScreenUpdating = False


With Sheets("Stage raw JE data")

Set rData = .Cells(1,1).CurrentRegion
Set rDataNoHeader = rData.Cells(2,1).Resize(rData.Rows.Count -1, rData.Columns.Count)


With .Sort
.SortFields.Clear
.SortFields.Add Key:=rDataNoHeader.Columns(17), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal
.SetRange rData
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With




Just thoughts and personal opinions

[sschwant]

Okay, I see what you mean about opening a zip extension file in a zip program ... I did that and opened it is 7 Zip and then opened the worksheets folder ... I get this view:

16394

Sheet 7 is huge ... but there's literally no data on it at all . . . not even any formulas ... how can that be ?

Assuming here that the sheets in the work book are numbered 1-11 left to right, correct ?

Steve

[sschwant]

Okay, ran Jacob's Excel Diet and that was awesome.

I'm also stepping through each one of macros, Ctrl F8 to cursor as I go - found where I need to make a correction .... just not sure of the proper Syntax.

Basically, just need to tell Excel to go down on



Sub new_copy_tbl_JE()


Sheets("tblJE").Select
Range("E2:I2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Actuals Consol").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A3").Select
Selection.End(xlDown).Select

Just need proper Syntax to tell Excel to offset down by one row ??


' ActiveCell.Offset -1, 0
' Selection.End (xlDown) - 1
'This is where the macro breaks down ... range A753
'is no longer relevant.
'Range("A753").Select
ActiveWindow.SmallScroll Down:=12
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
' Range("E747").Select
' Call Copy_FC_to_Consol
End Sub


e row ...

[mdmackillop]

Started by cleaning up code
Module 4

Sub Delete_All_Data()
Call Delete_FC_Details
Call Delete_Actuals_Consol_records
Call Delete_Data_on_Stage_raw_JE_Data_tab
Call Delete_AP_Accruals
Call Delete_Tbl_JE_data
Call Delete_AP_Qry_Data
Call Delete_IC_Transfers
Call Delete_Actuals_and_FC_Consol_data
End Sub


Sub Delete_FC_Details()
Sheets("FC Details").Range("A2").Resize(Rows.Count - 1, 5).Clear
End Sub


Sub Delete_Actuals_Consol_records()
With Sheets("Actuals Consol")
.Range("A2").Resize(Rows.Count - 1, 5).Clear
.Columns("E:E").Delete
End With
End Sub


Sub Delete_Data_on_Stage_raw_JE_Data_tab()
Sheets("Stage raw JE data").Range("A:T").Clear
End Sub


Sub Delete_AP_Accruals()
Sheets("AP Accls").Range("A2").Resize(Rows.Count - 1, 18).Clear
End Sub


Sub Delete_Tbl_JE_data()
Sheets("tblJE").Range("A2").Resize(Rows.Count - 1, 18).Clear
End Sub


Sub Delete_AP_Qry_Data()
Sheets("AP Qry Dataset").Columns("A:S").Clear
End Sub


Sub Delete_IC_Transfers()
Sheets("IC Transfers").Range("A2").Resize(Rows.Count - 1, 18).Clear
End Sub


Sub Delete_Actuals_and_FC_Consol_data()
With Sheets("Actuals and FC Consol")
.Range("A2").Resize(Rows.Count - 1, 2).ClearContents
.Range("D2").Resize(Rows.Count - 1, 3).ClearContents
End With
End Sub

[mdmackillop]

Module 3 - To date

Sub Stage_All_Data_and_Refresh_Pivot()
Call Stage_AP_data_step_1
Call Stage_JE_raw_Data_Step2
Application.Goto Sheets("PT").Range("A5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
MsgBox "Done"
End Sub


Sub Stage_AP_data_step_1()
'
' AP_data Macro
' After running Qry_Mrg in NCT database, copy paste values into the "AP Qry Dataset" tab onto cell A1.
' This section rearranges the columns prior to merging AP & JE data on "Actuals Consol" tab.


With Sheets("AP Qry Dataset")
.Activate
.Rows("1:1").Delete
.Cells.Font.Size = 10
With .Rows("1:1")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Font.Bold = True
End With
FreezeTopRow
.Cells.EntireColumn.AutoFit
.Columns("E:E").Cut .Range("R1")
.Range("S1").Formula = "Div"
.Columns("N:O").Cut .Range("T1")
With .Range("O1:S1").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End With
End Sub


Sub Stage_JE_raw_Data_Step2()
With Sheets("Stage raw JE data")
.Rows("1:1").Delete
With .Cells.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
With .Rows("1:1")
.RowHeight = 24.75
.Font.Bold = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End With
' Stage_JE_raw_data Macro - part 2
' Rearrange columns on worksheet after copying and pasting values onto blank sheet "Stage raw JE data"


With Sheets("Stage raw JE data")
.Columns("E:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("E1").Formula = "GroupID"
.Columns("G:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("G1").Formula = "Division"
.Range("P1").Formula = "VendorName"
.Columns("P:Q").Cut Range("H1")
.Range("H1").EntireColumn.AutoFit
End With
' Calls next macro - "purge" AP Accruals and Xfers
Call Purge_JE_Data
End Sub



Sub FreezeTopRow()
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub

[sschwant]

Thank you for digging in on this! I think I'm close to having fully debugged. If I bomb out again - will try running your batch of VB!

Best!

Steve

[mdmackillop]

Please don't quote what has just been posted. Quote selected areas only relevant to any query.

Where is your code to copy in the raw data?

[Paul_Hossler]

Assuming here that the sheets in the work book are numbered 1-11 left to right, correct ?

Steve

Not necessarily -- Sheet7 is the CodeName for the Worksheet named "FC Details"


16396


In the VBE, Project Explorer gives the "inards" of the VB Project (Control-R if not shown)


If you re-arrange worksheets, or give them a new .Name (e.g. "FC Details_2", the CodeName doesn't change

[SamT]

Okay, ran Jacob's Excel Diet and that was awesome.I thought you would like it. I keep a copy in MyPersonal.xls ad use it often.


Sheet 7 is huge ... but there's literally no data on it at all . . . not even any formulas ... how can that be ?

Assuming here that the sheets in the work book are numbered 1-11 left to right, correct ?
Those xml names are taken from the Sheet's Object.Name

In VBA, use the Menu "View" to make sure the Project Explorer and the Properties windows are open.

In the Project Explorer the Sheet names will be listed in pairs

Sheet1 (Sht Tab Name)

Where Sheet1 in the example is the Sheet Object's Name and the Name in the parenthesis is the name on the Sheet Tab in Excel.

In the Properties window, when focused on a Sheet, the names will be displayed in the opposite format

Name ... (Sheet1)
Name ... Sht Tab Name

In the Properties Window you can edit both the Sheet Object's Name and the Tab Name, as well as any other built-in Sheet Properties.