macro/sub to copy a specific ws from source wb to open wb and overwrite existing ws

[nycjdc]

Hi,

I want to, with the click of a button, copy into my open workbook a
worksheet from a closed workbook.

For example, open workbook is called openwb, source worksheet is called
Kenny, in workbook called Master.

Within openwb I want to be able to call a macro that:
extracts from Master a COPY of the worksheet called Kenny
overwrites unconditionally a worksheet named KennyCopy in my openwb.


Ideally, the contents that get written to the Kenny ws in openwb are
_pasted_ values because Kenny in the Master workbook is a huge number
of formulas and I'm only interested in the values in openwb.

In no way, will it be tolerated to have the chance of corruption of the
Master file, so ideally a temporary copy of Master workbook would be
used.

Is there a short macro that can be written to achieve the desired
effect?

Thank you for any suggestions.

 

[NickHK]

Break it down into suitable steps:

- Create copy of Master; Use Name "Oldfile" as "Copy of Oldfile"
- Open the copy
- Copy the desired range from Kenny
- PasteSpecial xlValues into the WS in openWB
- Close the copy
- Delete the copy; use Kill

NickHK

 

[nycjdc]

Hey Martin,

All works really great except for one tinny piece. How can I get around
it?

This line of code opens the copy of the source workbook:
Set wbSrc = Workbooks.Open(szDstDir & szWBSrcName, , True)
' open copy as read only


but the problem w/ the source workbook is that it has a bajillion links
in it. When the workbook is open it prompts the user to Update, Don't
Update or Help about the existing links.

Is there a way to get around this? I thought I heard of method where
the source document doesn't even need to be opened. Is that true?

Many thanks for all your help.


-nycjdc

 

[nycjdc]

Martin,

Got it sorted out. Thanks again.

-nycjdc