VBA code for Sheet1

E

eekkiimm

New Member
Joined
Apr 15, 2019
Messages
6
Hi all, i spent a good portion of the day trying to figure this out without any success. I created a macro that uses a system generated report to sort data and transfer it to a separate sheet. The thing is, the report is generated every day with sheet name as "Ledger Account Detail_20190415_" and tomorrow it will be named "...20190416_"
How do i make the macro look at "Sheet1" instead of the system given name? Or if its possible to maybe have the macro just look at only the portion of the system named sheet, for example "Ledger Account Detail*" Below is a portion of the code from my macro, I tried different variances of naming it Sheet(1).select, Worksheet("Sheet1"), etc. and none have worked for me.


ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Have you tried
Code: 
ActiveWorkbook.Sheet1.AutoFilter.Sort.
 
Upvote 0
Hi Michael, I just tried it and it gave me the same error. Object does not support this property or method
 
Upvote 0
[/QUOTE][/PHP]
Michael M said:
Maybe post all the code please?
Click to expand...


PHP: 
Sub Wires2()'' Wires2 Macro'' Keyboard Shortcut: Ctrl+q'    ChDir "U:\"    ActiveWorkbook.SaveAs Filename:="U:\wires2.xlsx", FileFormat:= _        xlOpenXMLWorkbook, CreateBackup:=False    Cells.Select    Selection.Font.Bold = True    Cells.EntireColumn.AutoFit    Rows("1:1").Select    With Selection        .HorizontalAlignment = xlCenter        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = False    End With    Selection.Font.Underline = xlUnderlineStyleSingle    Rows("2:2").Select    Selection.Delete Shift:=xlUp    Columns("D:D").Select    Columns("T:T").Select    Selection.Style = "Comma"    Range("R13").Select    Range("D:D,E:E,F:F,G:G,H:H").Select    Range("H1").Activate    Range("D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M").Select    Range("M1").Activate    Range("D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R").Select    Range("R1").Activate    Range("D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,V:V,W:W,X:X" _        ).Select    Range("X1").Activate    Range( _        "D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE" _        ).Select    Range("AE1").Activate    Selection.Delete Shift:=xlToLeft    Columns("B:B").Select    Selection.Cut    Columns("A:A").Select    Selection.Insert Shift:=xlToRight    Range("G1").Select    ActiveCell.FormulaR1C1 = "MIKE"    Rows("1:1").Select    Selection.AutoFilter    Range("B11").Select    ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter.Sort. _        SortFields.Clear    ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter.Sort. _        SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:= _        xlAscending, DataOption:=xlSortNormal    With ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter. _        Sort        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .SortMethod = xlPinYin        .Apply    End With    Range("F17").Select    Range("K2").Select    Range("J2").Select    Windows("wires2.xlsx").Activate    Range("T2").Select    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-19],RC[-18])"    Range("T2").Select    Selection.AutoFill Destination:=Range("T2:T10000"), Type:=xlFillDefault    Range("T2:T10000").Select    Columns("T:T").EntireColumn.AutoFit    Range("S2").Select    Windows("wires2.xlsx").Activate    ActiveCell.FormulaR1C1 = "=SUMIF(C[1],CONCATENATE(C[-18],C[-17]),C[-14])"    Range("S2").Select    Selection.AutoFill Destination:=Range("S2:S10000"), Type:=xlFillDefault    Range("S2:S10000").Select    Range("T9998").Select    Columns("S:S").Select    Selection.Style = "Comma"    Range("N12").Select    Sheets.Add After:=ActiveSheet    Sheets("Sheet1").Select    Sheets("Sheet1").Name = "Wires"    Windows("wires2.xlsx").Activate    Range("A1").Select    ActiveCell.FormulaR1C1 = "Amount"    Range("B1").Select    ActiveCell.FormulaR1C1 = "Name"    Range("C1").Select    ActiveCell.FormulaR1C1 = "='Ledger Account Detail_20190412_'!RC[4]"    Range("C2").Select    Range("A2").Select    Windows("wires2.xlsx").Activate    Range("A2").Select    ActiveCell.FormulaR1C1 = "='Ledger Account Detail_20190412_'!RC[18]"    Range("A2").Select    Selection.AutoFill Destination:=Range("A2:A10000"), Type:=xlFillDefault    Range("A2:A10000").Select    Columns("A:A").EntireColumn.AutoFit    Range("B2").Select    ActiveCell.FormulaR1C1 = "='Ledger Account Detail_20190412_'!RC[18]"    Range("B2").Select    Selection.AutoFill Destination:=Range("B2:B10000"), Type:=xlFillDefault    Range("B2:B10000").Select    Range("B2").Select    Columns("B:B").EntireColumn.AutoFit    Columns("A:B").Select    ActiveSheet.Range("$A$1:$B$10000").RemoveDuplicates Columns:=Array(1, 2), _        Header:=xlYes    Cells.Select    Selection.Font.Bold = True    With Selection.Font        .Name = "Calibri"        .Size = 14        .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.Font.Underline = xlUnderlineStyleSingle    With Selection        .HorizontalAlignment = xlCenter        .VerticalAlignment = xlBottom        .WrapText = False        .Orientation = 0        .AddIndent = False        .IndentLevel = 0        .ShrinkToFit = False        .ReadingOrder = xlContext        .MergeCells = False    End With    Rows("1:1").Select    Selection.AutoFilter    Range("C6").SelectEnd Sub
 
Upvote 0
Michael M said:
Maybe post all the code please?
Click to expand...

Sorry for the above, the one below is easier on the eyes.

Code: 
Sub Wires2()
'
' Wires2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ChDir "U:"
    ActiveWorkbook.SaveAs Filename:="U:\wires2.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    Cells.Select
    Selection.Font.Bold = True
    Cells.EntireColumn.AutoFit
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Underline = xlUnderlineStyleSingle
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Columns("D:D").Select
    Columns("T:T").Select
    Selection.Style = "Comma"
    Range("R13").Select
    Range("D:D,E:E,F:F,G:G,H:H").Select
    Range("H1").Activate
    Range("D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M").Select
    Range("M1").Activate
    Range("D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R").Select
    Range("R1").Activate
    Range("D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,V:V,W:W,X:X" _
        ).Select
    Range("X1").Activate
    Range( _
        "D:D,E:E,F:F,G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,V:V,W:W,X:X,Y:Y,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE" _
        ).Select
    Range("AE1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "MIKE"
    Rows("1:1").Select
    Selection.AutoFilter
    Range("B11").Select
    ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter.Sort. _
        SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Ledger Account Detail_20190412_").AutoFilter. _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F17").Select
    Range("K2").Select
    Range("J2").Select
    Windows("wires2.xlsx").Activate
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-19],RC[-18])"
    Range("T2").Select
    Selection.AutoFill Destination:=Range("T2:T10000"), Type:=xlFillDefault
    Range("T2:T10000").Select
    Columns("T:T").EntireColumn.AutoFit
    Range("S2").Select
    Windows("wires2.xlsx").Activate
    ActiveCell.FormulaR1C1 = "=SUMIF(C[1],CONCATENATE(C[-18],C[-17]),C[-14])"
    Range("S2").Select
    Selection.AutoFill Destination:=Range("S2:S10000"), Type:=xlFillDefault
    Range("S2:S10000").Select
    Range("T9998").Select
    Columns("S:S").Select
    Selection.Style = "Comma"
    Range("N12").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Wires"
    Windows("wires2.xlsx").Activate
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "='Ledger Account Detail_20190412_'!RC[4]"
    Range("C2").Select
    Range("A2").Select
    Windows("wires2.xlsx").Activate
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "='Ledger Account Detail_20190412_'!RC[18]"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A10000"), Type:=xlFillDefault
    Range("A2:A10000").Select
    Columns("A:A").EntireColumn.AutoFit
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "='Ledger Account Detail_20190412_'!RC[18]"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B10000"), Type:=xlFillDefault
    Range("B2:B10000").Select
    Range("B2").Select
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:B").Select
    ActiveSheet.Range("$A$1:$B$10000").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlYes
    Cells.Select
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .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.Font.Underline = xlUnderlineStyleSingle
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.AutoFilter
    Range("C6").Select
End Sub
 
Upvote 0
Is there just the one sheet in the workbook?
 
Upvote 0
How about
Code: 
Sub Wires2()
'
' Wires2 Macro
'
' Keyboard Shortcut: Ctrl+q
    Application.ScreenUpdating = False
    ChDir "U:"
    ActiveWorkbook.SaveAs FileName:="U:\wires2.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    Cells.Font.Bold = True
    Cells.EntireColumn.AutoFit
    With Rows(1)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .Font.Underline = xlUnderlineStyleSingle
    End With
    Rows(2).Delete Shift:=xlUp
    Columns("T:T").Style = "Comma"
    Range("D:R,V:AE").Delete
    Columns("B:B").Cut
    Columns("A:A").Insert Shift:=xlToRight
    Range("G1").Value = "MIKE"
    Rows("1:1").AutoFilter
    With Worksheets(1).AutoFilter.Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:= _
         xlAscending, DataOption:=xlSortNormal
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
    Range("T2:T10000").FormulaR1C1 = "=CONCATENATE(RC[-19],RC[-18])"
    Columns("T:T").EntireColumn.AutoFit
    Range("S2:S10000").FormulaR1C1 = "=SUMIF(C[1],CONCATENATE(C[-18],C[-17]),C[-14])"
    Columns("S:S").Style = "Comma"
    Sheets.Add(After:=ActiveSheet).Name = "Wires"
    Range("A1").Value = "Amount"
    Range("B1").Value = "Name"
    Range("C1").FormulaR1C1 = "='" & Sheets(1).Name & "'!RC[4]"
    Range("A2:A10000").FormulaR1C1 = "='" & Sheets(1).Name & "'!RC[18]"
    Range("A2:A10000").EntireColumn.AutoFit
    Range("B2:B10000").FormulaR1C1 = "='" & Sheets(1).Name & "'!RC[18]"
    Columns("B:B").EntireColumn.AutoFit
    Range("$A$1:$B$10000").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlYes
    With Cells.Font
        .Bold = True
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With Rows(1)
        .Font.Underline = xlUnderlineStyleSingle
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows(1).AutoFilter
    Range("C6").Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
443
John_w
J
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
762
Joe4
Joe4
E
  • Solved
A little VBA/Macro code adjustment
Replies
3
Views
279
rollis13
rollis13
J
  • Question Question
Rename a Sheet
Replies
2
Views
361
Peter_SSs
Peter_SSs
M
  • Question Question
Help with my code
Replies
9
Views
560
kevin9999
K

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

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