Recorded Macro Code

[Damian37]

Hello All,
I posted a previous thread, but I think I didn't offer enough evidence that I've tried to figure out the code I need to use to accomplish the task I need it to. In that effort I recorded a macro to see if it would at least give me the base code to view in order to make changes to suit what it is that I need the macro to do. However, upon looking at the base code, I'm having a little difficulty understanding how it's doing what I recorded, because I don't see any DIM declarations. I also see where it covers a range, however it doesn't look like previous code I've used when selecting a range. Here is the code that I got when recording the macro:

Sub FilterDataCopyPasteSave()
'
' FilterDataCopyPasteSave Macro
' Filter by Region; copy data; paste in new workbook; save new workbook

    ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:= _
        "Region Name"
    ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:= _
        "Open"
    ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:= _
        Array("Store Types"), Operator:=xlFilterValues
    Range("Table6[[#Headers],[Store Id]]").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Application.CutCopyMode = False
    ChDir "C:\Users\NTID Login\Desktop"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\NTID Login\Desktop\POC Region 3.30.2021.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Save
    ActiveWindow.SmallScroll Down:=-39
    ActiveWindow.Close
    Application.ActiveProtectedViewWindow.Edit
    ActiveWindow.Close
    Application.ActiveProtectedViewWindow.Edit
End Sub

Previous code I've used when copying data :

Sub Treat1()
Dim WkRg  As Range
Dim Objdic  As Object

Dim E  As Range
    With Sheets("AllData")
       
        .Range("AE2:AE" & .Range("AE" & Rows.Count).End(xlUp).Row).Copy _
        'looking to paste the copied data in to a new workbook, not a worksheet'
            Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(0, 0)
     
    End With
        Set WkRg = Sheets("Sheet1").Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
       
    With CreateObject("Scripting.Dictionary")
        For Each E In WkRg
            .Item(E.Value) = Empty
        Next E
        WkRg.Clear
        Sheets("Sheet1").Cells(1, 1).Resize(.Count, 1) = Application.Transpose(.Keys)
    End With
End Sub

Essentially, I'm trying to filter my data within multiple columns, copy it, and then paste it within a new workbook. Once data has been pasted into a new workbook, I would like to save the new workbook with a generic name that changes based on the current date and Region Name.

ActiveWorkbook.SaveAs Filename:= _

        "C:\Users\NTID Login\Desktop\POC Region Name & Format(Date, "mm.dd.yyyy")& ".xlsx", FileFormat:= _

        xlOpenXMLWorkbook, CreateBackup:=False

I appreciate any tips or information that can point me in the right direction. Thank you.

D.

 

[6StringJazzer]

Your recorded macro does just what you described except for the file name. You can simply replace the SaveAs in the recorded macro with the SaveAs you showed below.

Except that recorded macros are often not really want we want. Have you run it? Does it do what you want? If not, how is what you want different?

Recorded macros never have a Dim statement. Declaring variables is something that programmers do to manage values. The macro recorder does not need to do that. If you refer to Range("A1") 25 times, a programmer might declare a Range variable to save it, but the macro recorder will refer to Range("A1") 25 times.

The first thing I would do in your code is to explicitly identify the sheet instead of using ActiveSheet. But I don't know what sheet that is. In this example it is called Sheet With Table.

This may not be precisely what you want but it should put you on the right road:

Sub FilterDataCopyPasteSave()
'
' FilterDataCopyPasteSave Macro
' Filter by Region; copy data; paste in new workbook; save new workbook

   Dim TableSheet As Worksheet
   Dim NewWB As Workbook
   
   Set TableSheet = Worksheets("Sheet With Table")
   
   With TableSheet
   
      .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Region Name"
      .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open"
      .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("Store Types"), Operator:=xlFilterValues
      
      .Range("Table6[[#Headers],[Store Id]]").Select
      .Range(Selection, Selection.End(xlToRight)).Select
      .Range(Selection, Selection.End(xlDown)).Select
   
   End With
   
   Selection.Copy
   Set NewWB = Workbooks.Add
   
   With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
   
   NewWB.SaveAs Filename:="C:\Users\NTID Login\Desktop\POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
    
End Sub

 

[Damian37]

Your recorded macro does just what you described except for the file name. You can simply replace the SaveAs in the recorded macro with the SaveAs you showed below.

Except that recorded macros are often not really want we want. Have you run it? Does it do what you want? If not, how is what you want different?

Recorded macros never have a Dim statement. Declaring variables is something that programmers do to manage values. The macro recorder does not need to do that. If you refer to Range("A1") 25 times, a programmer might declare a Range variable to save it, but the macro recorder will refer to Range("A1") 25 times.

The first thing I would do in your code is to explicitly identify the sheet instead of using ActiveSheet. But I don't know what sheet that is. In this example it is called Sheet With Table.

This may not be precisely what you want but it should put you on the right road:

Sub FilterDataCopyPasteSave()
'
' FilterDataCopyPasteSave Macro
' Filter by Region; copy data; paste in new workbook; save new workbook

   Dim TableSheet As Worksheet
   Dim NewWB As Workbook
  
   Set TableSheet = Worksheets("Sheet With Table")
  
   With TableSheet
  
      .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Region Name"
      .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open"
      .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("Store Types"), Operator:=xlFilterValues
     
      .Range("Table6[[#Headers],[Store Id]]").Select
      .Range(Selection, Selection.End(xlToRight)).Select
      .Range(Selection, Selection.End(xlDown)).Select
  
   End With
  
   Selection.Copy
   Set NewWB = Workbooks.Add
  
   With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
  
   NewWB.SaveAs Filename:="C:\Users\NTID Login\Desktop\POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
   
End Sub

Thanks 6StringJazzer,
This does help a lot. The recorded macro does what I need it to do, however, I would like to set up a loop in order to filter off of each unique region name. There are currently 15 regions, and I would like to filter based on the three criteria, however, the first criteria would be different within each loop. Due to lack of VBA use, I've forgotten how to set up a loop to filter through each unique region name in order to copy the new filtered data. Finally, after copying the data for each region, and then pasting each regions data into a new workbook. I would also like to save each workbook, and have the region name be included within each new naming convention. If I were to use something like the below code, would it work the way I've described it?

NewWB.SaveAs Filename:="C:\Users\NTID Login\Desktop\POC "Region"" & Format(Date, "mm.dd.yyyy") & ".xlsx", _
    FileFormat:=xlOpenXMLWorkbook, _
    CreateBackup:=False

 

[Damian37]

Your recorded macro does just what you described except for the file name. You can simply replace the SaveAs in the recorded macro with the SaveAs you showed below.

Except that recorded macros are often not really want we want. Have you run it? Does it do what you want? If not, how is what you want different?

Recorded macros never have a Dim statement. Declaring variables is something that programmers do to manage values. The macro recorder does not need to do that. If you refer to Range("A1") 25 times, a programmer might declare a Range variable to save it, but the macro recorder will refer to Range("A1") 25 times.

The first thing I would do in your code is to explicitly identify the sheet instead of using ActiveSheet. But I don't know what sheet that is. In this example it is called Sheet With Table.

This may not be precisely what you want but it should put you on the right road:

Sub FilterDataCopyPasteSave()
'
' FilterDataCopyPasteSave Macro
' Filter by Region; copy data; paste in new workbook; save new workbook

   Dim TableSheet As Worksheet
   Dim NewWB As Workbook
  
   Set TableSheet = Worksheets("Sheet With Table")
  
   With TableSheet
  
      .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Region Name"
      .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open"
      .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("Store Types"), Operator:=xlFilterValues
     
      .Range("Table6[[#Headers],[Store Id]]").Select
      .Range(Selection, Selection.End(xlToRight)).Select
      .Range(Selection, Selection.End(xlDown)).Select
  
   End With
  
   Selection.Copy
   Set NewWB = Workbooks.Add
  
   With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
  
   NewWB.SaveAs Filename:="C:\Users\NTID Login\Desktop\POC Region " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
   
End Sub

Hi 6StringJazzer,
I tried out your code, and it is working the way I want it to, however, I tried to copy the same code to add a different region name and received a Run-time error '1004' error. The error detail is the "Select method of Range class failed". Here's the modified code I'm attempting to use based on the code you provided me:

Sub FilterByRegion()

' FilterDataCopyPasteSave Macro
' Filter by Region; copy data; paste in new workbook; save new workbook

  Dim TableSheet As Worksheet
  Dim NewWB As Workbook
 
   Set TableSheet = Worksheets("AllData")
 
  With TableSheet
 
     .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Beltway"
     .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open*"
     .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM ; 2.5", "XM; 2.6", "XM; 3", "XR"), Operator:=xlFilterValues
    
     .Range("Table6[[#Headers],[Store Id]]").Select
     .Range(Selection, Selection.End(xlToRight)).Select
     .Range(Selection, Selection.End(xlDown)).Select
 
  End With
 
  Selection.Copy
  Set NewWB = Workbooks.Add
 
  With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
 
   NewWB.SaveAs Filename:="C:\Users\NTID Login\Desktop\POC Beltway" & " " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
  
   With TableSheet
 
     .ListObjects("Table6").Range.AutoFilter Field:=3, Criteria1:="Big South"
     .ListObjects("Table6").Range.AutoFilter Field:=5, Criteria1:="Open*"
     .ListObjects("Table6").Range.AutoFilter Field:=4, Criteria1:=Array("CSC", "SX", "XM ; 2.5", "XM; 2.6", "XM; 3", "XR"), Operator:=xlFilterValues
    
     [B].Range("Table6[[#Headers],[Store Id]]").Select[/B]
     .Range(Selection, Selection.End(xlToRight)).Select
     .Range(Selection, Selection.End(xlDown)).Select
 
  End With
 
  Selection.Copy
  Set NewWB = Workbooks.Add
 
  With NewWB.Worksheets(1)
      .Paste
      Application.CutCopyMode = False
      .Cells.EntireColumn.AutoFit
   End With
 
   NewWB.SaveAs Filename:="C:\Users\NTID Login\Desktop\POC Big South" & " " & Format(Date, "mm.dd.yyyy") & ".xlsx", _
                FileFormat:=xlOpenXMLWorkbook, _
                CreateBackup:=False
                
End Sub

The second code snippet that I added for the region Big South stops at ".Range("Table6[[#Headers],[Store Id]]").Select". This is the line where I receive the error 1004 - "Select method of Range class failed". I'm unsure what I'm doing wrong with the second code snippet. I thought by simply changing the criteria for the region name within the second code snippet would work in creating a new workbook, however, after getting that error I'm not entirely sure what I need to change in order for the second code snippet to create another workbook for the next region. Any thoughts? Thank you very much for your help with this.

D.