Macro to Keep Sheet Names as Sheet1 and Sheet2

[djmnon]

I have a macro which I have created which basically filters out the data and copy pastes it into 2 different sheets (this is standard).

So whenever I run the Macro say 1st time it will show as Sheet1 and Sheet2
Whenever I run the Macro say 2nd time it shows as Sheet3 and Sheet4

Expected Result.

Everytime I run the Macro it the sheet names should be as Sheet1 and Sheet2

How do I do this?

Below is the Macro :- this is a Recorded Macro want to modify it by renaming the file names to Sheet1 and Sheet2 everytime

Sub dave()
'
' dave Macro
'

'
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$U$9995").AutoFilter Field:=8, Criteria1:= _
"Home Office"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Orders").Select
ActiveWindow.ScrollRow = 9851
ActiveWindow.ScrollRow = 9761
ActiveWindow.ScrollRow = 9656
ActiveWindow.ScrollRow = 9536
ActiveWindow.ScrollRow = 9372
ActiveWindow.ScrollRow = 8698
ActiveWindow.ScrollRow = 8473
ActiveWindow.ScrollRow = 7785
ActiveWindow.ScrollRow = 5704
ActiveWindow.ScrollRow = 5030
ActiveWindow.ScrollRow = 4851
ActiveWindow.ScrollRow = 4312
ActiveWindow.ScrollRow = 4012
ActiveWindow.ScrollRow = 3578
ActiveWindow.ScrollRow = 3444
ActiveWindow.ScrollRow = 3234
ActiveWindow.ScrollRow = 3144
ActiveWindow.ScrollRow = 2935
ActiveWindow.ScrollRow = 2830
ActiveWindow.ScrollRow = 2560
ActiveWindow.ScrollRow = 1707
ActiveWindow.ScrollRow = 1497
ActiveWindow.ScrollRow = 1378
ActiveWindow.ScrollRow = 1273
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 1138
ActiveWindow.ScrollRow = 1093
ActiveWindow.ScrollRow = 1063
ActiveWindow.ScrollRow = 1048
ActiveWindow.ScrollRow = 1018
ActiveWindow.ScrollRow = 1003
ActiveWindow.ScrollRow = 989
ActiveWindow.ScrollRow = 974
ActiveWindow.ScrollRow = 959
ActiveWindow.ScrollRow = 914
ActiveWindow.ScrollRow = 884
ActiveWindow.ScrollRow = 809
ActiveWindow.ScrollRow = 779
ActiveWindow.ScrollRow = 704
ActiveWindow.ScrollRow = 644
ActiveWindow.ScrollRow = 569
ActiveWindow.ScrollRow = 524
ActiveWindow.ScrollRow = 495
ActiveWindow.ScrollRow = 465
ActiveWindow.ScrollRow = 450
ActiveWindow.ScrollRow = 420
ActiveWindow.ScrollRow = 390
ActiveWindow.ScrollRow = 345
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 1
ActiveSheet.Range("$A$1:$U$9995").AutoFilter Field:=13, Criteria1:="East"
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A98").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
Sheets("Orders").Select
End Sub

 

[BSALV]

and if sheet1 and sheet2 excists already, it may empty those sheets and overwrite them ?

 

[djmnon]

and if sheet1 and sheet2 excists already, it may empty those sheets and overwrite them ?

Not empty the records but just rename the sheet names...even if the query overwrites the data and sheet names thats fine...these are standard filters which are run every month

Expected Steps as below

Month - March
Master Data-10000 records
Filter Columns 1,2,3 (Standard ones) and Copy paste data to Sheet1
Filter Columns 4,10,11 (Standard ones) and Copy Paste data to Sheet2

I created a Macro for the above
Run Macro - Filter Columns (these are standard Filters that don't change)

Paste into 2 sheets. 1st run the names are Sheet1 and Sheet2
2nd run it names it to Sheet4 and Sheet5
3rd run it names it to Sheet6 and Sheet7 and so on.


There 2 things that need fix

1st :- When I run the Macro

It should create 2 sheets named Sheet1, Sheet2
2nd When I run the Macro say in the month of April again it should run and Overwrite the Sheet1 and Sheet2 rather than renaming them or adding extra sheets (Sheets3 and Sheet4 etc)

I hope this makes sense

 

[BSALV]

i didn't test this, because no data, but it should be something like this.
It can be better if your data is 1 contigious block with currentregion ...

Sub dave()

     On Error Resume Next                                       'proceed in case of error (if sheets doesn't excist)
     Application.DisplayAlerts = False                          'give no alert
     Sheets("sheet1").Delete                                    'delete those sheets
     Sheets("sheet2").Delete
     Application.DisplayAlerts = True
     On Error GoTo 0

     With Sheets("Orders")                                      '<---------give here the name of the sheet, i think it's Orders
          .Range("A1").AutoFilter
          With .Range("$A$1:$U$9995")
               .AutoFilter Field:=8, Criteria1:="Home Office"   '1st filter
               .SpecialCells(xlVisible).Copy                    'the visible cells only
               Sheets.Add After:=ActiveSheet
               With ActiveSheet
                    .Paste
                    .Name = "SHEET1"
               End With

               .AutoFilter Field:=13, Criteria1:="East"         '2nd filter
               .SpecialCells(xlVisible).Copy
               Sheets.Add After:=ActiveSheet
               With ActiveSheet
                    .Paste
                    .Name = "SHEET2"
               End With
          End With
     End With

End Sub

 

[BSALV]

sorry, before the 2nd filter, PERHAPS?, the 1st has to be reset

              End With

              .autofilter    '<---------------ADD THIS
              .AutoFilter Field:=13, Criteria1:="East"         '2nd filter

 

[djmnon]

Thanks...should I replace it with the above Macro I have created?

How do I upload the dataset here...not able to find an option

 

[BSALV]

you can use the XL2BB-tool for a limited number of cells/rows.
In this case, 10 rows with non sensitive (no existing names, etc) but representive data is enough.

 

[djmnon]

Superb stuff it seems like it works...Il get in touch if Im stuck anywhere.

But just one doubt :- If there are multiple filters in the recorded macro then how will be adding the multiple filters in the query to say Sheet1 has 10 filters then how do we modify the query.

.AutoFilter Field:=8, Criteria1:="Home Office"
.AutoFilter Field:=8, Criteria1:="United States"
.AutoFilter Field:=8, Criteria1:="Kentucky"

thats how it will go?

 

[djmnon]

I was able to do that Thanks a ton for that.

I need a further help where in I want to combine sheet1 and sheet2 and append & paste on Sheet3

The image is from Sheet1 so I want the data present in Sheet1 and Sheet2 on Sheet3

When considering Sheet1 we need the headers and where Sheet1 data ends the next row the Sheet2 data must be appended and pasted.

Thanks a ton in advance

 

[BSALV]

so everything that is copied to sheet2 must also be copied to sheet3 ?
Currentregion takes the contigious range around A1, so you don't have to oversize.

Sub dave()

     On Error Resume Next                                       'proceed in case of error (if sheets doesn't excist)
     Application.DisplayAlerts = False                          'give no alert
     Sheets("sheet1").Delete                                    'delete those sheets
     Sheets("sheet2").Delete
     Application.DisplayAlerts = True
     On Error GoTo 0

     With Sheets("Orders")                                      '<---------give here the name of the sheet, i think it's Orders
          .Range("A1").AutoFilter
          With .Range("$A$1").currentregion
               .AutoFilter Field:=8, Criteria1:="Home Office"   '1st filter
               .SpecialCells(xlVisible).Copy                    'the visible cells only
               Sheets.Add After:=ActiveSheet
               With ActiveSheet
                    .Paste
                    .Name = "SHEET1"
               End With

               .Offset(1).SpecialCells(xlVisible).Copy          'the visible cells without header
               With Sheets("sheet3")                            'to a 3rd sheet
                    With .Range("A" & Rows.Count).End(xlUp).Offset(1)     'next free A-cell
                         .Paste
                    End With
               End With

               .AutoFilter Field:=13, Criteria1:="East"         '2nd filter
               .SpecialCells(xlVisible).Copy
              ......