VBA to copy multiple tabs from closed workbook(no formulas pasta values).

[falcons07076]

Hello I need some help please. I am very new to any type of VBA so I may need extra guidance as well. Basically, what I need to do is to copy 3 tabs from "workbook #1" which contains like 15 tabs to workbook #2 which contains only the 3 tabs that i need to copy. Tee data needs to be copy paste values in order to work. I have to copy these tabs daily replacing the existing tabs every time. I don't want it to be a new file everyday I just want the data in each of the tabs to be updated every time i run the macro. Basically I want to replace the tabs in workbook 2 with the same tabs everyday.

• Workbook#1 is titled: "Daily Treasury Reporting (Live).xlsx" = this file is the active worksheet that has many formulas and it's where people update data daily.
• The path is: C:\Users\mstevens\Steel People\Daily Reporting - General
• Workbook#2 is titled "Daily Reporting Export.xlsx" = this is the file that gets updated from workbook 1 daily.
• The path to workbook#2 C:\Users\mstevens\Steel People\Daily Reporting - General\Source Documents\Daily Files
• The tabs that need to be copied from Workbook one are titled: "Cash Export Summary" / "Cash Export Summary(Balances) / Bank Details (Reference)

I would like to run the macro from Workbook 2 if possible. Please let me know if I explained this clearly. I really appreciate your help with this.

 

[GlennJ]

I'm not sure how much you know about VBA. Try putting this code in a Module in Daily Reporting Export.xlsx

Sub CopyTabs()


Workbooks.Open ("C:\Users\mstevens\Steel People\Daily Reporting - General")
ThisWorkbook.Activate
    Windows("Daily Treasury Reporting (Live).xlsx").Activate
    Sheets("Cash Export Summary").Select
    Cells.Select
    Selection.Copy
    Windows("Daily Reporting Export.xlsx").Activate
    Sheets("Cash Export Summary").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Treasury Reporting (Live).xlsx").Activate
    Sheets("Cash Export Summary(Balances) ").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Daily Reporting Export.xlsx").Activate
    Sheets("Cash Export Summary(Balances) ").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Daily Treasury Reporting (Live).xlsx").Activate
    Sheets("Bank Details (Reference)").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Daily Reporting Export.xlsx").Activate
    Sheets("Bank Details (Reference)").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    
    
End Sub

 

[falcons07076]

Thank you so very much for sending this over. It's much appreciated. It's giving me an error on the first line that's stating the attached. Daily Reporting General is actually the file path the file that it needs to open is the Daily Treasury Reporting (Live).xlsx. Not sure if that helps. Again, this is much appreciated Glenn.

 

[GlennJ]

Try this


VBA Code:

Sub CopyTabs()


Workbooks.Open ("C:\Users\mstevens\Steel People\Daily Reporting - General\Daily Treasury Reporting (Live)")
ThisWorkbook.Activate

Workbooks.Open ("C:\Users\mstevens\Steel People\Daily Reporting - General\Source Documents\Daily Files\Daily Reporting Export")
ThisWorkbook.Activate

Windows("Daily Treasury Reporting (Live).xlsx").Activate
Sheets("Cash Export Summary").Select
Cells.Select
Selection.Copy
Windows("Daily Reporting Export.xlsx").Activate
Sheets("Cash Export Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Daily Treasury Reporting (Live).xlsx").Activate
Sheets("Cash Export Summary(Balances) ").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Daily Reporting Export.xlsx").Activate
Sheets("Cash Export Summary(Balances) ").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Daily Treasury Reporting (Live).xlsx").Activate
Sheets("Bank Details (Reference)").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Daily Reporting Export.xlsx").Activate
Sheets("Bank Details (Reference)").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select


End Sub

[/CODE]

 

[falcons07076]

Thank you so very much for sending this over. It's much appreciated. It's giving me an error on the first line that's stating the attached. Daily Reporting General is actually the file path the file that it needs to open is the Daily Treasury Reporting (Live).xlsx. Not sure if that helps. Again, this is much appreciated Glenn.

And I have

 

[falcons07076]

Hi Glenn-

It works!! You are the absolute best. I am getting the error message attached though. If it's something I need to fix easily please let me know. You have made my week!

 

[GlennJ]

Let's give this a try.

Sub CopyTabs()


Workbooks.Open ("C:\Users\mstevens\Steel People\Daily Reporting - General\Daily Treasury Reporting (Live)")
ThisWorkbook.Activate

Workbooks.Open ("C:\Users\mstevens\Steel People\Daily Reporting - General\Source Documents\Daily Files\Daily Reporting Export")
ThisWorkbook.Activate

Windows("Daily Treasury Reporting (Live).xlsx").Activate
Sheets("Cash Export Summary").Select
Cells.Select
Selection.Copy
Windows("Daily Reporting Export.xlsx").Activate
Sheets("Cash Export Summary").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Daily Treasury Reporting (Live).xlsx").Activate
Sheets("Cash Export Summary(Balances) ").Select
Range("A").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Daily Reporting Export.xlsx").Activate
Sheets("Cash Export Summary(Balances) ").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Daily Treasury Reporting (Live).xlsx").Activate
Sheets("Bank Details (Reference)").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Daily Reporting Export.xlsx").Activate
Sheets("Bank Details (Reference)").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select


End Sub

 

[falcons07076]

Thanks so much

 

[TotallyConfused]

Hello

I haven't looked carefully at any of the code here but there is one thing I have noticed. I'm certainly no expert at coding, so please correct me if I'm wrong, but I notice that all the files mentioned here are of the *.xlsx format. It's been my understanding that that type of format won't run VBA. The file needs to be a *.XLSM format.

TotallyConfused

 

[GlennJ]

Hello

I haven't looked carefully at any of the code here but there is one thing I have noticed. I'm certainly no expert at coding, so please correct me if I'm wrong, but I notice that all the files mentioned here are of the *.xlsx format. It's been my understanding that that type of format won't run VBA. The file needs to be a *.XLSM format.

TotallyConfused

Your correct. This person must have known to save it as a macro enabled file for it to run or did little research.