VB Copy from outside workbook into current

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
256
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!
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,728
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
256
Office Version
  1. 2019
Platform
  1. Windows
Am I supposed to change anything in that code? It doesn't do anything. No error, no nothing.
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
256
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Just one sheet, "Sheet1"
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,728
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
 
Solution

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
256
Office Version
  1. 2019
Platform
  1. Windows
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?
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
256
Office Version
  1. 2019
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,135
Messages
5,628,901
Members
416,352
Latest member
Lunox01

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
Top