Replacing Worksheets Using An Exterior Workbook

soteman2005

New Member
Joined
Nov 24, 2005
Messages
32
Hi,

I would like to open a workbook whose name is defined in named range "Welcome_Overrides_Factbase_Selected_Name" in a workbook called "Test1_Adam_20_12_05_2", then copy 4 sheets, ("sheet1","sheet2","sheet3","sheet4") from the just opened workbook to "Test1", replacing sheets ("sheet1","sheet2","sheet3","sheet4") , which already exist in the workbook. Then close the initially open workbook.

Is this possible, and could I save the new workbook with a name that takes the original workbooks name, except the Test1 part i.e. "Test2_Adam_20_12_05_2"?

I am quite stuck on this one so any help would be greatly appreciated.


Thanks

Adam
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Adam

Does this work?
Code:
Dim wbThis As Workbook
Dim wbOpen As Workbook
Dim arrSheets

Set wbThis = ThisWorkbook
Set wbOpen = Workbooks.Open(Range("Welcome_Overrides_Factbase_Selected_Name"))

arrSheets = Array("sheet1","sheet2","sheet3","sheet4")

wbThis.Worksheets(arrSheets).Delete

wbOpen.Worksheets.Copy After:=wbThis.Worksheets(wbThis.Worksheets.Count)

wbOpen.Close
 

Watch MrExcel Video

Forum statistics

Threads
1,118,388
Messages
5,571,842
Members
412,420
Latest member
grace_abar
Top