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

falcons07076

New Member
Joined
Nov 11, 2020
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I'm not sure how much you know about VBA. Try putting this code in a Module in Daily Reporting Export.xlsx

VBA Code:
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
 
Upvote 0
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.
 

Attachments

  • Capture.JPG
    Capture.JPG
    31.8 KB · Views: 7
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
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!
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    36.3 KB · Views: 7
Upvote 0
Let's give this a try.

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
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
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top