Macros running on source file in place of target file

Q

questforexcel

Board Regular
Joined
Jan 18, 2019
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
Hi,

I am having issues performing my repetitive tasks using macros.

I am new to it and would appreciate some help.

I recorded a macro but when I try to run it, it starts working on the source file where I had recorded the steps rather than working on the target file.

Also it keeps giving me error 1004. I searched for this on MS help page and other platforms this issue was on earlier Office versions. I have Office 2016 and it still gives me this issue.

Could you please help or guide on how to go about macros. I might be missing something and would appreciate your guidance.

Thank you
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi Sure.

Below is the code.
Code: 
Sub FS()
'
' FS Macro
'


'
    Rows("2:8").Select
    Selection.Style = "Normal"
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Rows("3:6").Select
    Selection.Delete Shift:=xlUp
    Rows("2:4").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.UnMerge
    Range("E1").Select
    With Selection.Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    ActiveCell.FormulaR1C1 = "Om"
    Rows("7:7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Normal"
    Rows("2:4").Select
    Rows("2:4").EntireRow.AutoFit
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("A6:J6").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 6299648
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Columns("B:B").Select
    Range("B6").Activate
    Selection.Insert Shift:=xlToRight
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "Ledger No"
    Range("A2:A4").Select
    Selection.Cut
    Range("B2").Select
    ActiveSheet.Paste
    Range("B6").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    ActiveWorkbook.Save
    Columns("B:B").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 7.43
    Columns("C:J").Select
    Columns("C:J").EntireColumn.AutoFit
    Range("F13").Select
    Columns("F:F").ColumnWidth = 21.57
    Columns("D:D").Select
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E6").Select
    Columns("E:E").ColumnWidth = 15.57
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    ActiveCell.FormulaR1C1 = "Account Name"
    Range("E7").Select
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("H6").Select
    Columns("D:D").ColumnWidth = 14
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "Beginning Yr Bal"
    Range("H7").Select
    Windows("CC - FY17.xlsb").Activate
    Range("O12").Select
    Windows("CC Detail by Account 2018.xlsb").Activate
    Columns("K:K").Select
    Range("K7").Activate
    Selection.Insert Shift:=xlToRight
    Range("K6").Select
    ActiveCell.FormulaR1C1 = "Cl Daily Bal"
    Range("K7").Select
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Columns("L:L").Select
    Range("L6").Activate
    Selection.Delete Shift:=xlToLeft
    Range("L6").Select
    ActiveCell.FormulaR1C1 = "Check Daily Bal"
    Range("L7").Select
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("M6").Select
    ActiveCell.FormulaR1C1 = "Account Type"
    Range("M7").Select
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "FY - GL"
    Sheets.Add After:=ActiveSheet
    Cells.Select
    ActiveWindow.DisplayGridlines = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("A3").Select
    Windows("CC - FY17.xlsb").Activate
    Sheets("FY17 Legend").Select
    Range("B7").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("B2:C63").Select
    Selection.Copy
    ActiveSheet.Next.Select
    ActiveSheet.Previous.Select
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("B2").Select
    ActiveSheet.Paste
    Cells.Select
    Application.CutCopyMode = False
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Selection.ColumnWidth = 8.57
    Columns("E:E").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireColumn.Hidden = True
    Range("B10").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("B17").Select
    Selection.End(xlDown).Select
    Rows("65:65").Select
    Range("B65").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Rows("65:65").Select
    Range("B65").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Hidden = True
    Range("D64").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("B1").Select
    ActiveWorkbook.Save
    ActiveSheet.Previous.Select
    Range("C8").Select
    Windows("CC - FY17.xlsb").Activate
    ActiveSheet.Previous.Select
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""-"",RC[-2])),RC[-1],0)"
    Range("E6").Select
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("E7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""-"",RC[-2])),RC[-1],0)"
    Range("C7").Select
    Selection.Copy
    Range("B7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("B8").Select
    Windows("CC - FY17.xlsb").Activate
    Range("B7").Select
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""-"",RC[1])),RC[1],R[-1]C)"
    Range("B8").Select
    Selection.Copy
    Range("C8").Select
    Selection.End(xlDown).Select
    Range("B10607").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Range("E7").Select
    Windows("CC - FY17.xlsb").Activate
    Range("E7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("B8").Select
    Selection.End(xlDown).Select
    Range("E10607").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("E8:E10607").Select
    Range("E10607").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("H7").Select
    Windows("CC - FY17.xlsb").Activate
    Range("H7").Select
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("H8").Select
    ActiveSheet.Paste
    Range("H7").Select
    ActiveSheet.Paste
    Range("H8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("E9").Select
    Selection.End(xlDown).Select
    Range("H10607").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("H7:H10607").Select
    Range("H10607").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Windows("CC - FY17.xlsb").Activate
    Application.CutCopyMode = False
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("B6").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("B6:M10607").Select
    Selection.AutoFilter
    Range("F6").Select
    ActiveSheet.Range("$B$6:$M$10610").AutoFilter Field:=5, Criteria1:="="
    Range("C6").Select
    ActiveSheet.Range("$B$6:$M$10610").AutoFilter Field:=2, Criteria1:="="
    Rows("9:9").Select
    Range("D9").Activate
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    Range("C6").Select
    ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=2
    Range("F6").Select
    ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5
    Range("H6").Select
    ActiveWorkbook.Save
    Range("I8").Select
    Windows("CC - FY17.xlsb").Activate
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("K11").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("F10").Select
    Selection.End(xlUp).Select
    ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5, Criteria1:="="
    Range("F7").Select
    Application.CutCopyMode = False
    Range("F6").Select
    ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5
    Range("F10").Select
    Selection.End(xlUp).Select
    Range("F7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("F7"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
    Columns("F:F").EntireColumn.AutoFit
    Selection.NumberFormat = "[$-en-CA]d/mmm/yy;@"
    Range("F8").Select
    ActiveWorkbook.Save
    Range("F7").Select
    ActiveCell.FormulaR1C1 = "1-Sep-2017"
    Range("F7").Select
    Selection.Copy
    Range("F6").Select
    ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5, Criteria1:="="
    Range("E8").Select
    Selection.End(xlDown).Select
    Range("F10059").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("F8:F10059").Select
    Range("F10059").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.Paste
    Range("F10059").Select
    Selection.End(xlUp).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("F6").Select
    ActiveSheet.Range("$B$6:$M$10074").AutoFilter Field:=5
    Range("K7").Select
    Windows("CC - FY17.xlsb").Activate
    Range("L10").Select
    Selection.Copy
    Application.CutCopyMode = False
    Range("K10").Select
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("K7").Select
    Range("K8").Select
    ActiveSheet.Paste
    Range("K8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F11").Select
    Selection.End(xlDown).Select
    Range("K10074").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("K8:K10074").Select
    Range("K10074").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("K10073").Select
    Selection.End(xlUp).Select
    Range("L7").Select
    Windows("CC - FY17.xlsb").Activate
    Range("L5").Select
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Application.CutCopyMode = False
    Columns("L:L").Select
    Range("L6").Activate
    Selection.Insert Shift:=xlToRight
    Range("L6").Select
    ActiveCell.FormulaR1C1 = "Cl Mon Bal"
    Range("L8").Select
    Windows("CC - FY17.xlsb").Activate
    Range("L8").Select
    Selection.Copy
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("L8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Copy
    Range("K8").Select
    Selection.End(xlDown).Select
    Range("L10074").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Selection.End(xlUp).Select
    Range("L11").Select
    Selection.End(xlDown).Select
    Range("L10073").Select
    Selection.End(xlUp).Select
    Range("N7").Select
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "FY - Legend"
    Sheets("FY - GL").Select
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Windows("CC - FY17.xlsb").Activate
    Range("N8").Select
    Windows("CC Detail by Account 2018.xlsb").Activate
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    ActiveSheet.Paste
    Range("N7").Select
    Windows("CC - FY17.xlsb").Activate
    Range("N6").Select
    Selection.Copy
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("M4").Select
    Windows("CC Detail by Account 2018.xlsb").Activate
    Range("N4").Select
    ActiveSheet.Paste
    Range("N7").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(SUMPRODUCT(NOT(ISERR(SEARCH(RC[-9],'FY - Legend'!R3C2:R63C2)))*1),'FY - Legend'!R2C2,""Balance Sheet"")"
    Range("N4").Select
    Selection.ClearContents
    Range("N7").Select
    Selection.Copy
    Range("L8").Select
    Selection.End(xlDown).Select
    Range("N10074").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("N7:N10074").Select
    Range("N10074").Activate
    ActiveSheet.Paste
    Selection.End(xlUp).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Font.Bold = True
    Range("B4").Select
    ActiveWorkbook.Save
    Range("B6").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "FY - GL!R6C2:R10074C14", Version:=6).CreatePivotTable TableDestination:= _
        "", TableName:="PivotTable1", DefaultVersion:=6
    
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "FY MoM P&L"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type").CurrentPage _
        = "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type")
        .PivotItems("Balance Sheet").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Type"). _
        EnableMultiplePageItems = True
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    ActiveWindow.DisplayGridlines = False
    Range("B1").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").AutoGroup
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarters").Orientation = _
        xlHidden
    Range("C4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems("2017"). _
        ShowDetail = True
    Range("F4").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Years").PivotItems("2018"). _
        ShowDetail = True
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Cl Mon Bal"), "Sum of Cl Mon Bal", xlSum
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Range("B6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("B6:N54").Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
    ActiveWindow.ScrollColumn = 1
    Columns("B:N").Select
    ActiveWorkbook.Save
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("D12").Select
    ActiveWorkbook.Save
    ActiveWindow.SmallScroll Down:=-18
End Sub
 
Last edited by a moderator:
Upvote 0
It is a long code, but it would have been ideal had this code worked. But somehow , the code always ends up messing my initial file. Thank you
 
Upvote 0
Looks like it will start working on whatever file is open, then when you see "Windows("CC - FY17.xlsb").Activate" and similar it starts working on those books instead.

There's a lot of code here, what I would do is step through the code, you do this by pressing F8 to go from line to line, use that to see where it's going wrong and also to see any unnecessary steps that you can eliminate to clean it up a little.

The 1004 is caused when you've referenced something that doesn't exist. It may be a sheet in your original book that isn't in the one you're trying to use this on.


Examples of cleaning up:

These look to be identical, you only need one and you only need what you are changing.
Code: 
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With
[/FONT][/COLOR]


They may be shortened to:
Code: 
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]


Or even this, if you are only changing the vertical alignment

Code: 
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With
[/FONT][/COLOR]

Have a step through, see if you can clean some up, and share any errors with the line they error on.
 
Last edited:
Upvote 0
Thank you for posting your quick response.

Could you help me with an example on how to shorten these in the VBA codes. I tried googling and searching on Macros and VBAs but they popped up most basic codes and processes.

Would appreciate your help. Thanks


mrshl9898 said:
Looks like it will start working on whatever file is open, then when you see "Windows("CC - FY17.xlsb").Activate" and similar it starts working on those books instead.

There's a lot of code here, what I would do is step through the code, you do this by pressing F8 to go from line to line, use that to see where it's going wrong and also to see any unnecessary steps that you can eliminate to clean it up a little.

The 1004 is caused when you've referenced something that doesn't exist. It may be a sheet in your original book that isn't in the one you're trying to use this on.


Examples of cleaning up:

These look to be identical, you only need one and you only need what you are changing.
Code: 
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With
[/FONT][/COLOR]


They may be shortened to:
Code: 
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].HorizontalAlignment = xlGeneral[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].WrapText = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].Orientation = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].AddIndent = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].IndentLevel = 0[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ShrinkToFit = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].ReadingOrder = xlContext[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].MergeCells = False[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With[/FONT][/COLOR]


Or even this, if you are only changing the vertical alignment

Code: 
[COLOR=#333333][FONT=Verdana]With Selection[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana].VerticalAlignment = xlBottom[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]End With
[/FONT][/COLOR]

Have a step through, see if you can clean some up, and share any errors with the line they error on.
Click to expand...
 
Upvote 0
I think some should appear to you as you step through the code. There's a lot that could be done here. But for me personally it's more of a learning process over time.

Here are a few though:

Code: 
    Columns("B:B").Select
    Range("B6").Activate
    Selection.Insert Shift:=xlToRight
    
    'shorter
    Columns("B:B").Insert Shift:=xlToRight

Code: 
    Columns("B:B").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 7.43
    Columns("C:J").Select
    Columns("C:J").EntireColumn.AutoFit
    Range("F13").Select
    Columns("F:F").ColumnWidth = 21.57
    Columns("D:D").Select
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").Select
    'shorter
    Columns.AutoFit

Things like this you'll see are unneeded as you step through, switching between windows.

Code: 
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate

There's also a few times you're trying to find the last row or select a range with ctrl + down. Reading this would help

https://www.thespreadsheetguru.com/...to-find-the-last-row-or-last-column-using-vba
 
Upvote 0
Thank you for suggestion and response. Would surely look through this.

mrshl9898 said:
I think some should appear to you as you step through the code. There's a lot that could be done here. But for me personally it's more of a learning process over time.

Here are a few though:

Code: 
    Columns("B:B").Select
    Range("B6").Activate
    Selection.Insert Shift:=xlToRight
    
    'shorter
    Columns("B:B").Insert Shift:=xlToRight

Code: 
    Columns("B:B").EntireColumn.AutoFit
    Columns("B:B").ColumnWidth = 7.43
    Columns("C:J").Select
    Columns("C:J").EntireColumn.AutoFit
    Range("F13").Select
    Columns("F:F").ColumnWidth = 21.57
    Columns("D:D").Select
    Columns("D:D").EntireColumn.AutoFit
    Columns("E:E").Select
    'shorter
    Columns.AutoFit

Things like this you'll see are unneeded as you step through, switching between windows.

Code: 
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate
    Windows("CC - FY17.xlsb").Activate
    Windows("CC Detail by Account 2018.xlsb").Activate

There's also a few times you're trying to find the last row or select a range with ctrl + down. Reading this would help

https://www.thespreadsheetguru.com/...to-find-the-last-row-or-last-column-using-vba
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
New Laptop - VBA Script will not run using VBS file manually or via Task Scheduler
Replies
0
Views
467
jaihawk8
J
S
  • Question Question
Merge two table from different csv files
2 3
Replies
24
Views
668
Soly_90
S
I
  • Question Question
Unable to Open or Copy Corrupted Excel Files on Network Drive
Replies
1
Views
212
Candyman8019
C
P
  • Solved
Programatically create Personal Macro Workbook using VBA
Replies
5
Views
306
Domenic
D
S
  • Question Question
Is there an alternate way to look into an excel sheet instead of connections and queries?
Replies
6
Views
153
SPS41
S

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top