VB Copy from outside workbook into current

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
351
Office Version
  1. 2019
Platform
  1. Windows
Hello smart VB Excel peeps!

Let's say that my active sheet is called "Sheet1", and I have an entire sheet in another workbook which I need to import into my active sheet ("Sheet1"). The sheet in the other workbook right now is titled "2021 Budget - Sales Ony", but that "2021" may change next year, and it could even change completely to something like "2022 Plan". There will always be a year in the title, but the rest may or may not change.

Is there a macro I can attach to a button in my "Sheet1", which will copy the entire page from the other sheet, and paste into "Sheet1" regardess of the name (maybe if it's the only other sheet open)?It sounds like a stupid question to me, because with a changing name how would Excel know where to copy from. But figured I'd ask before trying to rethink a different way.

Thanks!
 
What is the name of the worksheet in your second workbook you are wanting to copy? Is it "2021 Budget - Sales Ony"? The code should work... Make sure there's no space in from of 2021
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Am I supposed to change anything in that code? It doesn't do anything. No error, no nothing.
 
Upvote 0
There's our problem.

VBA Code:
Sub MoveSheets()

Dim ws As Worksheet
Dim wb As Workbook
Dim wb2 As Workbook

For Each wb In Application.Workbooks
    If wb.Name Like Year(Now()) & "*" Then
        Set wb2 = wb
    End If
Next wb

Set wb = ThisWorkbook

wb2.Sheets("Sheet1").Copy Before:=wb.Sheets(1)


End Sub
 
Upvote 0
Solution
First time I ran it, I got a Compile error:
1614915636642.png


I then ran it a second time and it worked. Is that normal?
 
Upvote 0
Also, won't this macro then just keep adding on an additional sheet every time this macro is run? If yes, then that will keep increasing the file size and ultimately have lots of unnecessary sheets attached. Is there an easy fix to just make it keep replacing the previous?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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