Copy Sheets from Closed Workbook into Open Active Workbook without linking

**CHRISOK** · 02-15-2018, 11:39 PM

Need to copy Sheet1 and Sheet2 from a CLOSED workbook sitting on desktop called: "AR-template" into my OPEN/ACTIVE Workbook without linking. (and without messing up the formatting/column widths, etc.)
C:/Users/brook/Desktop/AR-template

Don't want to specifically hard-code in the name the open/active workbook because it will vary frequently...
(as long as I make sure that's the only OPEN/ACTIVE workbook then it will be a safe copy/transfer)

Note: the open/active workbook will not hold any duplicate tabs called "Sheet1" or "Sheet2" so there shouldn't be any conflicts during the copy.
I guess it might be a good thing to have it do one of the following (if it accidentally found a duplicate when it was about to copy)
(a) MsgBox: "Sheet1" already exists in destination file (OR--- EVEN BETTER THE NEXT ALTERNATIVE BELOW)
(b) simply add the "(2)" like this: "Sheet1 (2)" and proceed w/ the copy without bothering w/ a msgbox.

Position placement would be great if they can be placed at the far left (as the first 2 sheets) - but if not sure how to do that - that's fine, I can move them around manually after the copy takes place.

Tried searching existing posts but the posts were either never answered w/ solutions or are different in need.
Thanks in advance!

**Alf** · 02-16-2018, 03:11 AM

Perhaps a macro like this then may be of help?

Please Login or Register  to view this content.

I'm not sure of the file name (AR-template.xlsx) so you have to change that if this is false. The move command will delete the sheet1 and sheet2 from the "AR-template.xlsx" file but this does not matter as the line "ActiveWindow.Close SaveChanges:=False" will close the "AR-template.xlsx" without saving any changes so your original "AR-template.xlsx" will stay intact.

ThisWorkbook is a command that refer to the workbook where you run this macro from so the name of this file does not matter. If ThisWorkbook only contains sheet3 and sheet4 it will after you run this macro contain sheets in this order Sheet2, Sheet1, Sheet3 and Sheet4.

If this Workbook contains Sheet2 and Sheet3 it will after the macro is run contain Sheet2(2), Sheet1 and Sheet3.

Alf

**CHRISOK** · 02-16-2018, 04:23 PM

That's it Alf! It worked!
I had to change all the slashes to lean the opposite direction first -- but then it worked perfectly!
"C:/Users/brook/Desktop/AR-template.xlsx" to "C:\Users\brook\Desktop\AR-template.xlsx"
Thanks -- this is wonderful!
REPUTATION kudos to you! Much appreciated!

**Alf** · 02-16-2018, 04:54 PM

Thanks for feedback and rep.

Good that you spoted my mistake re slash and backslash. You know there are times where I'm really
impressed by my own stupidity and this is one of those occasions..

Alf

Copy Sheets from Closed Workbook into Open Active Workbook without linking

LinkBack

Thread Tools

Rate This Thread

Display

Copy Sheets from Closed Workbook into Open Active Workbook without linking

Re: Copy Sheets from Closed Workbook into Open Active Workbook without linking

Re: Copy Sheets from Closed Workbook into Open Active Workbook without linking

Re: Copy Sheets from Closed Workbook into Open Active Workbook without linking

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] vba code to copy a specific sheet from a closed workbook to the active workbook

[SOLVED] vba copy all sheets from a closed workbook in a specified directory to active workbook

Open closed workbook, copy ranges from multiple sheets into an active Mastersheet

[SOLVED] Code to copy data from a closed workbook and paste in active workbook using named range.

Run Macro in closed workbook and copy specific coloumn in active workbook

Copy 2 sheets from a open workbook and paste to a closed workbook

Linking between Active and Closed Workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions