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

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.

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

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" wrote in message
ups.com...
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.

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

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


Martin Fishlock wrote:
nycjdc:

Try this little one.

'-----------------------------------
Option Explicit

Sub dojob()
'where the master.xls is
Const szSrcDir As String = "C:\Documents and Settings\admin\Desktop\" '
with the trailing slash
' where the openwb is used to store the copy of master.xls
Const szDstDir As String = "C:\Documents and Settings\admin\Desktop\" '
with the trailing slash

Const szWBSrcOrgName As String = "Master.xls"
Const szWBSrcName As String = "MasterCopyCanDelete.xls"
Const szWSSrcName As String = "Kenny"
Const szWSDstName As String = "KennyCopy"

' assumes global variable openwb
Dim openwb As Workbook
Set openwb = ActiveWorkbook

Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim fs As Object

On Error GoTo clean_up

Set fs = CreateObject("Scripting.FileSystemObject")

fs.CopyFile szSrcDir & szWBSrcOrgName, szDstDir & szWBSrcName, True '
copy file

Set wbSrc = Workbooks.Open(szDstDir & szWBSrcName, , True) ' open copy
as read only
Set wsSrc = wbSrc.Worksheets(szWSSrcName)
' copy and paste special values
wsSrc.Cells.Copy
wsSrc.Cells.PasteSpecial xlPasteValues

' copy the worksheet into the openwb
wsSrc.Cells.Copy openwb.Worksheets(szWSDstName).Cells
' close the copy master
wbSrc.Close False
' delete the copy master
clean_up:
On Error Resume Next
Kill szDstDir & szWBSrcName
Set wsSrc = Nothing
Set wbSrc = Nothing
End Sub
'------------------------------------
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"nycjdc" wrote:

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.

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

Martin,

Got it sorted out. Thanks again.

-nycjdc


nycjdc wrote:
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


Martin Fishlock wrote:
nycjdc:

Try this little one.

'-----------------------------------
Option Explicit

Sub dojob()
'where the master.xls is
Const szSrcDir As String = "C:\Documents and Settings\admin\Desktop\" '
with the trailing slash
' where the openwb is used to store the copy of master.xls
Const szDstDir As String = "C:\Documents and Settings\admin\Desktop\" '
with the trailing slash

Const szWBSrcOrgName As String = "Master.xls"
Const szWBSrcName As String = "MasterCopyCanDelete.xls"
Const szWSSrcName As String = "Kenny"
Const szWSDstName As String = "KennyCopy"

' assumes global variable openwb
Dim openwb As Workbook
Set openwb = ActiveWorkbook

Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim fs As Object

On Error GoTo clean_up

Set fs = CreateObject("Scripting.FileSystemObject")

fs.CopyFile szSrcDir & szWBSrcOrgName, szDstDir & szWBSrcName, True '
copy file

Set wbSrc = Workbooks.Open(szDstDir & szWBSrcName, , True) ' open copy
as read only
Set wsSrc = wbSrc.Worksheets(szWSSrcName)
' copy and paste special values
wsSrc.Cells.Copy
wsSrc.Cells.PasteSpecial xlPasteValues

' copy the worksheet into the openwb
wsSrc.Cells.Copy openwb.Worksheets(szWSDstName).Cells
' close the copy master
wbSrc.Close False
' delete the copy master
clean_up:
On Error Resume Next
Kill szDstDir & szWBSrcName
Set wsSrc = Nothing
Set wbSrc = Nothing
End Sub
'------------------------------------
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"nycjdc" wrote:

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.