VB Copy from outside workbook into current

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
226
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!
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
Is the other workbook open? Will its name or location change?

if open:
VBA Code:
Sub MoveSheets()



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

Set wb2 = Workbooks("Book2.xlsm") ''change as needed
Set wb = ThisWorkbook

For Each ws In wb2.Sheets
    If ws.Name Like Year(Now()) & "*" Then
    wb2.Sheets(ws.Name).Copy Before:=wb.Sheets(1)
    End If
Next ws



End Sub
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thx Mrshl9898! Sorry I thought I was clear in my introductory setup of the question. The sheet "can" be open if it has to in order to work. As for the name/loc changing, that is the point. I don't know. The point of my question is to cover me in case it does change, in that then the user could theoretically just open the sheet then press a button in my main sheet.

As for your much appreciated solution. that returned a compile error on this line:
Set wb2 = Workbooks("Book2.xlsm") ''change as needed

I thought maybe your comment in the code to "change as needed" meant that I would need to replace the "Book2" part with the current title of the sheet. Although I tried to do that (only to return a "runtime error 9", if that is the case then that would defeat the purpose. My point is that I'm wondering if there is a way to do it w/o the need to do change the code every time. Apologies if this is not the case, but just communicating my concern based on what I'm seeing so far.

Thx!
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
We seem to be having a communication problem with sheet (tab) and book (file). You seem to be calling them both sheets. I understand the sheet name will change, will the book also?

Set wb2 = Workbooks("Book2.xlsm")- replace Book2.xlsm with your book name and extension (.xlsx,.xlsm etc)

Here it searches that book for a sheet named after this year to copy, eg. 2021xxxx

If ws.Name Like Year(Now()) & "*" Then
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
226
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Ok, sorry for confusion. Yes, the book name will change too. In essense, I will have a sheet in a book which will need to absorb a different sheet in another book once that book has been received. I want to just be able to open the new book, then go back to my book & press button which will pull the sheet from the new book and place it into my book somehow..
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708
That's what my macro should be doing. Just change the book name.

Or did you want the book name to be dynamic also? Will it also have the year in its title?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,708

ADVERTISEMENT

Updated to loop through open workbooks, looking for "2021***" based off the current year

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

For Each ws In wb2.Sheets
    If ws.Name Like Year(Now()) & "*" Then
    wb2.Sheets(ws.Name).Copy Before:=wb.Sheets(1)
    End If
Next ws

End Sub
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Sorry, maybe I'm misunderstanding what exactly to change in your code.
The only thing I changed was this line.

Set wb2 = Workbooks("Book2.xlsm") ''change as needed

I changed "Book2" to the name of my 2nd workbook, and changed the ext to .xlsx since that is the file type of the 2nd workbook. But then I run the macro, literally nothing happens, no error, just nothing. Then I tried just changing the file type to .xlsx without altering the "Book2" part, and that returns a runtime error 9.
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Yes, again the book name will likely (not absolutely) change. Every file that I will need to use will 100% have a year in the name, and that year will either be the same as the current year, or after. The rest of the words in the title could change though. For instance, the next 4 workbooks could possibly look like this:
2022 Plan numbers
2022 Plan Revisions
2023 Budget numbers (annual)
2024 Budget/Plan by Month For Year
 

Watch MrExcel Video

Forum statistics

Threads
1,126,932
Messages
5,621,676
Members
415,849
Latest member
PhoenixRising2015

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