I wish I could synthesize this problem better, but basically:

I have a macro that will copy and paste values on 8 identical sheets (just with different data). Then I add three columns into each worksheet. The macro will work when I call it within the workbook. However, when I call it within another macro from another workbook, the line to add 3 columns into the workbook throws an error... for just two of the sheets. Does anyone have a clue on the problem?

Sales Dollars by Month and Sales Units by Month are the offending sheets.


Sub Sales_Report()
Dim LstRow As Long, LstClm As Long, rptRange As Range, LstSal As Date, Sht As Worksheet, Title As String, i As Long

ThisWorkbook.RefreshAll
ThisWorkbook.Save

Application.Calculation = xlCalculationManual

For i = 8 To 1 Step -1

Select Case i
Case 1: Set Sht = MD: Title = "Sales Dollars by Month"
Case 2: Set Sht = MU: Title = "Sales Units by Month"
Case 3: Set Sht = CD: Title = "Sales Dollars by Cycle"
Case 4: Set Sht = CU: Title = "Sales Units by Cycle"
Case 5: Set Sht = WD: Title = "Sales Dollars by Week"
Case 6: Set Sht = WU: Title = "Sales Units by Week"
Case 7: Set Sht = DD: Title = "Sales Dollars by Day"
Case 8: Set Sht = DU: Title = "Sales Units by Day"
End Select

LstSal = Workbooks("Sales_Compile").Worksheets("Compile").Range("G4").End(xlDown).Value

Sht.Activate

'Paste Pivot Values
Sht.Cells.Copy
Sht.Cells.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Rows("1:2").ClearContents

'Define Range
LstRow = Sht.Range("A3").End(xlDown).Row

'Add columns to indicate Sample Quantity, On Hand, and On Order
Sht.Range("E:G").Columns.Insert

Sht.Cells(3, 5).Value = "Sample Quantity"
Sht.Cells(4, 5).Formula = "=SUMIFS(DATA.xlsm!Samples[Quantity],DATA.xlsm!Samples[Generic ID],$B4)"

Sht.Cells(3, 6).Value = "OH"
Sht.Cells(4, 6).Formula = "=SUMIFS(DATA.xlsm!OH[OH],DATA.xlsm!OH[PID],$A4)"

Sht.Cells(3, 7).Value = "OO"
Sht.Cells(4, 7).Formula = "=SUMIFS(DATA.xlsm!Orders[OO],DATA.xlsm!Orders[PID],$A4)"

Sht.Range("E4:G" & LstRow - 1).FillDown

'Add Total columns for On Hand and On Order. Do NOT sum Sample quantity - the sum will have duplicates.
Sht.Cells(LstRow, 6).Formula = "=SUBTOTAL(9, F4:F" & LstRow - 1 & ")"
Sht.Cells(LstRow, 7).Formula = "=SUBTOTAL(9, G4:G" & LstRow - 1 & ")"

Sht.Calculate

'Paste values for Sample Quantity, On Hand, and On Order
Sht.Range(Sht.Cells(4, 5), Sht.Cells(LstRow - 1, 7)).Copy
Sht.Range(Sht.Cells(4, 5), Sht.Cells(LstRow - 1, 7)).PasteSpecial xlPasteValues

'Format new columns
Sht.Range(Sht.Cells(4, 5), Sht.Cells(LstRow, 7)).NumberFormat = "#,##0"
Sht.Range(Sht.Cells(4, 5), Sht.Cells(LstRow, 7)).HorizontalAlignment = xlCenter

'Define Range - continued
LstClm = Sht.Range("A3").End(xlToRight).Column + 1


'Fix Totals to update automatically to filters
Sht.Cells(LstRow, 11).Formula = "=SUBTOTAL(9, K4:K" & LstRow - 1 & ")"
Sht.Range(Sht.Cells(LstRow, 11), Sht.Cells(LstRow, LstClm)).FillRight


'Formatting
Set rptRange = Range(Sht.Cells(3, 1), Sht.Cells(LstRow, LstClm))

'Label Total
Cells(3, LstClm).Value = "Total"

'Outside Borders
With rptRange.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Color = -8776723
.Weight = xlThick
End With
With rptRange.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Color = -8776723
.Weight = xlThick
End With
With rptRange.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Color = -8776723
.Weight = xlThick
End With
With rptRange.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = -8776723
.Weight = xlThick
End With

'Inside Borders
With rptRange.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Color = -8776723
.Weight = xlThin
End With
With rptRange.Borders(xlInsideHorizontal)
.LineStyle = xlDash
.Color = -8776723
.Weight = xlThin
End With

'Title Borders
With Range(Sht.Cells(3, 1), Sht.Cells(3, LstClm)).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = -8776723
.Weight = xlMedium
End With
With Range(Sht.Cells(LstRow, 1), Sht.Cells(LstRow, LstClm)).Borders(xlEdgeTop)
.LineStyle = xlDash
.Color = -8776723
.Weight = xlMedium
End With
With Range(Sht.Cells(3, LstClm), Sht.Cells(LstRow, LstClm)).Borders(xlEdgeLeft)
.LineStyle = xlDash
.Color = -8776723
.Weight = xlMedium
End With

'Bold Lines
Range(Sht.Cells(3, 1), Sht.Cells(3, LstClm)).Font.Bold = True
Range(Sht.Cells(LstRow, 1), Sht.Cells(LstRow, LstClm)).Font.Bold = True
Range(Sht.Cells(3, LstClm), Sht.Cells(LstRow, LstClm)).Font.Bold = True

'Text Formatting
Range(Sht.Cells(3, 11), Sht.Cells(3, LstClm)).ColumnWidth = 11.43
Range(Sht.Cells(3, 11), Sht.Cells(3, LstClm)).VerticalAlignment = xlTop
Sht.Columns("A:J").AutoFit
Range(Sht.Cells(4, 3), Sht.Cells(LstRow, 4)).HorizontalAlignment = xlLeft

Sht.Range("A3:J3").VerticalAlignment = xlCenter
Sht.Range("3:3").WrapText = True
Sht.Range("3:3").RowHeight = 42

'Add Filters

Sht.Range("A" & LstRow).EntireRow.Insert
Sht.Range("A" & LstRow).RowHeight = 1
Range(Sht.Cells(3, 1), Sht.Cells(LstRow - 1, LstClm)).AutoFilter

Sht.AutoFilter.Sort.SortFields.Clear
Sht.AutoFilter.Sort.SortFields.Add Key:=Range(Sht.Cells(3, LstClm - 1), Sht.Cells(LstRow - 1, LstClm - 1)), Order:=xlDescending
Sht.AutoFilter.Sort.Apply

'Freeze Panes
Sht.Activate
Sht.Range("A1").Select
ActiveWindow.FreezePanes = False
Sht.Range("E4").Select
ActiveWindow.FreezePanes = True

'Final Touch
Sht.Range("A1").Value = Title
Sht.Range("A1").Font.Size = 14

Sht.Range("C1").Value = "Sales updated as of " & LstSal

Range(Sht.Cells(3, 11), Sht.Cells(3, LstClm - 13)).Columns.Group
Sht.Outline.ShowLevels ColumnLevels:=1

Application.Goto Sht.Cells(1, LstClm - 2), True
Sht.Range("A2").Select

Next i

Application.Calculation = xlCalculationAutomatic

ThisWorkbook.SaveAs "\\psf\Home\Dropbox\Birchbox\Sales & Inventory Reporting\Back-end\TEST Sales History", FileFormat:=51, Password:="", WriteResPassword:="bbpt"

End Sub