Copying and pasting from one sheet to several ones

[bearcub]

I have a file where I have a data dump from our database and I would to extract data from it for different offices. It could be run several times a day or several times a week since the data is always changing.

The user will do the following steps the file:

1) Copy the data dump onto a new sheet (leaving the original data dump intact - I already have this macro).
2) Remove the second sheet of certain titles and blanks (I already have this macro
3) Clear data from the existing sheets from the prior download (these would be the 4 destination sheets listed by office) - have this one done too.
4) Once the old data has been cleared from these sheets I have to go back to the newly named sheet (with the new data), filter it several times and paste data to the existing sheets. The filters that will be applies will be San Francisco, Los Angeles, Sacramento, Portland.

The problem I'm having is once I've cleared the old data I have to select the new sheet - with a new name - and filter it per city and copy each filtered data to the appropriate sheet. How do I refer to a sheet which is going to be different each month. I have used an input box to allow the user to name the new sheet. I used the variable ShtName which I assigned to the input box. In my file, the new sheet is called January 2018. someone else might name it Feb 18 or 2018 March, etc. I have to use some sort of variable to reference the sheet that was just created in the file.

This is the macro that I'm running for one destination sheet (I don't know if it is possible to use a for Next loop since they all have different names):

[B]Sub CopytoGoldenGateSCC()
'
' Copy Current Sheet to San Francisco sheet
'
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.AutoFilter
    ActiveWindow.SmallScroll ToRight:=17
    ActiveSheet.Range("$A$1:$BX$2282").AutoFilter Field:=38, Criteria1:= _
        "San Francisco"
    ActiveSheet.Cells.Select
    Selection.Copy
    Sheets("San Francisco").Select
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste
End Sub[/B]
[code]/

The first line of code refers to the source sheet (the one that was copied from the original). How do I refer to this source sheet if I'm running 4 different macros ? Or is there a way to loop through 4 of the 6 sheets in the file (the original, the copy and the 4 offices - the offices are the ones will get the data from the copy).

Hopefully, my explanation is confusing. Sorry in advance if it 

Is there a more efficient way of doing what I want to do?

Michael

 

[dmt32]

Hi,
If you are filtering from a master to region sheets then maybe code I developed for others here with similar requirement will do what you want.

Have a look here:https://www.mrexcel.com/forum/excel...t-another-based-value-column-first-sheet.html

Following instructions given in #Post 2

Code is dynamic & only requires you to click on the field heading (column with region names) to be filtered. Code checks if region sheets exist if not, create it. It will clear any existing data and copy new data to it.


Hope Helpful

Dave

 

[bearcub]

I'm test this out in my file today and try to add the filter column. It does look like it will simply my code a lot because right now I have separate routines for each office. I'll let you know if it works, thank you for sharing!



Michael