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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
257
John_w
J
J
  • Question
Rename a Sheet
Replies
2
Views
112
Peter_SSs
Peter_SSs
M
  • Question
Help with my code
Replies
9
Views
394
kevin9999
K
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
210
Coco1
C
B
  • Question
How to Create a Loop to ignore BLANK CELLS
Replies
3
Views
238
Alex Blakenburg
A

Forum statistics

Threads
1,215,059
Messages
6,122,917
Members
449,093
Latest member
dbomb1414

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