Copying from 1 Master WorkBook to Multiple Workbooks?

[AESP920]

I have a master file with 3 tabs in it that have to be copied/pasted into 9 other workbooks based on Region. Im hoping theres an easier way to copy and paste the data from each tab into each workbook in their own tabs.
So for example,
1. If in the Master, in Tab "QTD Revenue", column A = "California" then copy all rows into the "California" workbook into the "Qtd Revenue" tab.
2. If in the Master, in Tab "Backlog", column A = "California" then copy all rows into the "California" workbook into the "Backlog" tab.
3. If in the Master, in Tab "Services", column A = "California" then copy all rows into the "California" workbook into the "Services" tab.
Same thing then goes for if its "New York" , "Florida", etc.
Then I dont know if this is possible, I have pivot tables that correspond to each tab and I was hoping that after the data is copied/pasted into the "California" workbook that the pivot tables are updated accordingly?
And to make things even more complicated, the Master is not in the same folder as the Regionals. Its one folder up from the Regionals.
I copy and paste manually everyweek into 9 different workbooks and Im hoping theres an easier way to do this.

Any help on this would be fantastic.
Thanks in advance!
AESP

 

[Makrini]

Is Column "A" always only one region? I.E in Every Tab in the Master Workbook - Column A = California, Column B = New York - etc?

In this case the easiest way is to Macro record what you do for starters - then change all the "hard coded" workbook names into Variables.

Whilst this is not the cleanest way of doing it - if you haven't done a lot of VBA coding it is the easiest way to learn

 

[AESP920]

No. Column A will consist of all the regions. So it will have California, NY, Florida all in Column A.

 

[Makrini]

Ok Then - The way I would do it would be VBA code with Advanced Filters...

Sheets(wrCopyfromSheet).Columns(wrCopyfromRange).AdvancedFilter Action:=xlFilterCopy, _
CopytoRange:=Sheets(ThisSheet).Columns(wrCopyfromRange), CriteriaRange:=Crit, Unique:=False


Then copy your filtered Data to the specific Workbooks...

(Following is my code I use as a class library whenever I want to filter specific data from a workbook to another based on a set criteria)


Option Explicit

Dim wbDataWorkbook As String
Dim wbOriginWorkbook As String
Dim wrCopyfromSheet As String
Dim wrCopyfromRange As String
Dim wrHeaderRange As String
Dim wrCriteriaRange As String
Dim wrCopyToSheet As String



Public Property Get DataWorkbook() As String
DataWorkbook = wbDataWorkbook
End Property

Public Property Let DataWorkbook(ByVal vNewValue As String)
wbDataWorkbook = vNewValue
End Property
Public Property Get OriginWorkbook() As String
OriginWorkbook = wbOriginWorkbook
End Property

Private Sub Class_Initialize()
wbOriginWorkbook = ActiveWorkbook.Name
End Sub

Public Property Get CopyfromRange() As String
CopyfromRange = wrCopyfromRange
End Property

Public Property Let CopyfromRange(ByVal vNewValue As String)
wrCopyfromRange = vNewValue
End Property

Public Property Get CopyFromSheet() As String
CopyFromSheet = wrCopyfromSheet
End Property

Public Property Let CopyFromSheet(ByVal vNewValue As String)
wrCopyfromSheet = vNewValue
End Property

Public Property Get CopytoSheet() As String
CopytoSheet = wrCopyToSheet
End Property

Public Property Let CopytoSheet(ByVal vNewValue As String)
wrCopyToSheet = vNewValue
End Property
Public Property Get HeaderRange() As String
HeaderRange = wrHeaderRange
End Property

Public Property Let HeaderRange(ByVal vNewValue As String)
wrHeaderRange = vNewValue
End Property
Public Property Get CriteriaRange() As String
CriteriaRange = wrCriteriaRange
End Property

Public Property Let CriteriaRange(ByVal vNewValue As String)
wrCriteriaRange = vNewValue
End Property

Public Sub Filter_IT(Append As Boolean)
Dim Header As Range
Dim Crit As Range

Dim ThisSheet As String
Dim OriginSheet

OriginSheet = ActiveSheet.Name


Set Header = Range(wrHeaderRange)
Set Crit = Range(wrCriteriaRange)

Workbooks.Open wbDataWorkbook, updatelinks:=False, ReadOnly:=True

Sheets.Add

ThisSheet = ActiveSheet.Name
If Append = False Then Workbooks(wbOriginWorkbook).Sheets(wrCopyToSheet).Range("A2:Z10000").ClearContents

Range(wrHeaderRange).Value = Header.Value

Sheets(wrCopyfromSheet).Columns(wrCopyfromRange).AdvancedFilter Action:=xlFilterCopy, _
CopytoRange:=Sheets(ThisSheet).Columns(wrCopyfromRange), CriteriaRange:=Crit, Unique:=False

Sheets(ThisSheet).Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
AppendRange (Append)

Application.CutCopyMode = False
ActiveWorkbook.Close savechanges:=False
Sheets(OriginSheet).Select
End Sub

Private Sub AppendRange(Append As Boolean)

If Append Then
Workbooks(wbOriginWorkbook).Sheets(wrCopyToSheet).Range("A1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Else
Workbooks(wbOriginWorkbook).Sheets(wrCopyToSheet).Range("A2").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End If
End Sub