Macro to copy and paste multiple sheets into a new workbook

[Fiveshorter]

Hi,

I was wondering if anyone can helop me build a macro for a report i do every day. I have a master file that has multiple sheets. Every day i copy and paste some of these sheets into a new workbook.
In my master file i am only concerned about 3 sheets, MasterF, Customer, Country. I copy and paste customer into a new sheet and country into a new sheet (i only copy and paste the data to keep file size down). For my masterF file there are multiple filters, the data is in a standard table with standard filters on it. I filter my column entitled "Complete" to just yes and filter country to just "UK" then copy and paste the data into a new sheet (copying the data to keep file size down). Then i go back into the masterF file and unfilter. Then i filter the "complete" column to just "NO", country column to USA and NAME column to all except NOT AVAILABLE.

Result :

MasterF (A)
[TABLE="width: 500"]
<tbody>[TR]
[TD]CustID[/TD]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Order ID[/TD]
[TD]Complete[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tim[/TD]
[TD]UK[/TD]
[TD]67[/TD]
[TD]YES[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Daenerys[/TD]
[TD]UK[/TD]
[TD]190[/TD]
[TD]YES[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]


MasterF (B)
[TABLE="width: 500"]
<tbody>[TR]
[TD]CustID[/TD]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Order ID[/TD]
[TD]Complete[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Emma[/TD]
[TD]USA[/TD]
[TD]54[/TD]
[TD]NO[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


Country
[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust ID[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]123124[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]45353[/TD]
[TD]UK[/TD]
[/TR]
</tbody>[/TABLE]


Customer
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Cust ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]6543[/TD]
[TD]Damian[/TD]
[/TR]
[TR]
[TD]7645[/TD]
[TD]David[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheets in Master excel Sheet

Customer sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]6543[/TD]
[TD]Damian[/TD]
[/TR]
[TR]
[TD]7645[/TD]
[TD]David[/TD]
[/TR]
</tbody>[/TABLE]


Country sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust ID[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]123124[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]45353[/TD]
[TD]UK[/TD]
[/TR]
</tbody>[/TABLE]

MasterF sheet
[TABLE="width: 500"]
<tbody>[TR]
[TD]CustID[/TD]
[TD]Name[/TD]
[TD]Country[/TD]
[TD]Order ID[/TD]
[TD]Complete[/TD]
[TD]QTY[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jon[/TD]
[TD]USA[/TD]
[TD]34[/TD]
[TD]YES[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Emma[/TD]
[TD]USA[/TD]
[TD]54[/TD]
[TD]NO[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Tim[/TD]
[TD]UK[/TD]
[TD]67[/TD]
[TD]YES[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sansa[/TD]
[TD]UK[/TD]
[TD]78[/TD]
[TD]NO[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Daenerys[/TD]
[TD]UK[/TD]
[TD]190[/TD]
[TD]YES[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fiveshorter]

Thank you so much for this, this saves so much of my time hopefully other people will find this useful as well. Just a quick note, is there a way for the program to paste as special values as I have formulas in some of the sheets and when I run it the formulas are also pasted?

 

[frank_AL]

Okay, code has been modified to copy only Values and and Number Formats.
As info, this is accomplished by using .PasteSpecial xlPasteValuesAndNumberFormats

Option Explicit
Sub CopyToWorkbooks()
Dim cfwb As Workbook
Dim ctwb As Workbook
Dim cfws As Worksheet
Dim ctws As Worksheet
Dim ws As Worksheet
Dim cfrng As Range
Dim ctrng As Range
Dim lastrow As Long
Dim ctlr As Long

Set cfwb = ThisWorkbook

'   Open New Workbook and add/name Worksheets
Workbooks.Add
Set ctwb = ActiveWorkbook

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master(A)"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master(B)"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Customer"
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Country"
Application.DisplayAlerts = False
ctwb.Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

'   Copy data from Customer Worksheet and Paste in new Workbook
Set cfws = cfwb.Sheets("Customer")
Set ctws = ctwb.Sheets("Customer")
lastrow = cfws.Cells(Rows.Count, "A").End(xlUp).Row
With cfws
    .Range(.Cells(1, 1), .Cells(lastrow, 2)).Copy
End With
ctws.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

'   Copy data from Country Worksheet and Paste in new Workbook
Set cfws = ThisWorkbook.Sheets("Country")
Set ctws = ctwb.Sheets("Country")
lastrow = cfws.Cells(Rows.Count, "A").End(xlUp).Row
With cfws
    .Range(.Cells(1, 1), .Cells(lastrow, 2)).Copy
End With
ctws.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

'   Copy data from MasterF Worksheet for Complete = "YES" and Country = "UK"
    Set cfws = ThisWorkbook.Sheets("MasterF")
    
'   Clear any existing Filters
    On Error Resume Next
    cfws.AutoFilterMode = False
    Set ctws = ctwb.Worksheets("Master(A)")
    lastrow = cfws.Cells(Rows.Count, "A").End(xlUp).Row
    Set ctrng = ctws.Range("A1")

'   Apply new Filter
    Set cfrng = cfws.Range("A1:F" & lastrow)
    cfrng.AutoFilter
    cfrng.AutoFilter Field:=3, Criteria1:="UK"
    cfrng.AutoFilter Field:=5, Criteria1:="YES"
    cfrng.SpecialCells(xlCellTypeVisible).Copy
    ctrng.PasteSpecial xlPasteValuesAndNumberFormats

'   Copy data from MasterF Worksheet for Complete = "NO" and Country = "USA"
'    Set cfws = ThisWorkbook.Sheets("MasterF")
    Set ctws = ctwb.Worksheets("Master(B)")
    Set ctrng = ctws.Range("A1")

'   Clear any existing Filters and apply new Filter
    cfws.AutoFilterMode = False
    Set cfrng = cfws.Range("A1:F" & lastrow)
    cfrng.AutoFilter
    cfrng.AutoFilter Field:=3, Criteria1:="USA"
    cfrng.AutoFilter Field:=5, Criteria1:="NO"
    cfrng.SpecialCells(xlCellTypeVisible).Copy
    ctrng.PasteSpecial xlPasteValuesAndNumberFormats

MsgBox ("All copying has been completed")

End Sub

 

[Fiveshorter]

Thank you very much for the help on this one, works perfectly

 

[frank_AL]

Glad I could help!