Export Sheet Values to new workbook

[mp]

I have a workbook with many sheets
One sheet is called "overview"
This has summary of various other sheet's info
thus it refers to other sheets' cells
I want to do following with a vba command
save overview sheet as separate workbook with one sheet only
replace functions and references in each cell with current value(and format)
(that way even though original workbook is updated over time
I'll have an archive of the overview at various dates in past)

any tips on an easy way to do that appreciated.
so far options I can think of:
1)
save original workbook so changes aren't lost
change the formulas and references on the original "overview" sheet,
file saveas new name
delete all sheets but "overview"
hopefully that will leave original workbook intact (though it will no longer
be open or active)

2)
copy "overview" sheet in original workbook to a new temp sheet
replace formulas with values in new sheet
somehow export new sheet to new workbook (i'll have to research how to do
that)
delete new sheet from existing workbook
that way I can still be in original workbook(if that's important which may
not be a big deal)

3)
i'm sure there's a better way but drawing a blank, any ideas?

something like:
Sub ExportSheetValues(SourceSheetname as String, newBookname as String)
....implementation here....
End Sub

thanks
mark

 

[Donald Guillett]

The easy way is to just copy\paste values> right click on the sheet
tab>create a copy to a new workbook in the top box. Save>close
original withOUT saving.

 

[GS]

Another way...

With Sheets("overview")
.UsedRange.Value = .UsedRange.Value
.Copy 'opens a new wkb with a copy of the sheet
End With

 

[mp]

Another way...

With Sheets("overview")
.UsedRange.Value = .UsedRange.Value
.Copy 'opens a new wkb with a copy of the sheet
End With

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

awesome, thanks
that works beautifully
mark

 

[mp]

Another way...

With Sheets("overview")
.UsedRange.Value = .UsedRange.Value
.Copy 'opens a new wkb with a copy of the sheet
End With

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

the only problem is I have to be very careful to close
the master file without saving as the formulas have been overwritten
in the original

i'm working on a workaround for that
Sub CopyOverview()
With Sheets("overview")
.UsedRange.Value = .UsedRange.Value 'this destroys refs in original
.Copy 'opens a new wkb with a copy of the sheet and focus moves to new
wbk
End With

'save and close new workbook
ActiveWorkbook.SaveAs CurrentFolder & _
"\Overview.xls"
ActiveWorkbook.Close

'so far so good.
'but now i need to be sure *not* to save the original workbook
'I have to close without saving and reopen
'to get around that, somehow go back to original and undo the "pasting" of
values

'get back to original and undo overwritting of values on top of formulas
OriginalWorkbook.Activate
Call UndoPasteValues(ActiveWorkbook) '<<<< how to do this?

End Sub

 

[Clif McIrvin]

the only problem is I have to be very careful to close
the master file without saving as the formulas have been overwritten
in the original

Untried ... perhaps something like this:

With Sheets("overview")
.Copy 'opens a new wkb with a copy of the sheet
End With
' does this make the activesheet the new copy?
' if so, then

With activesheet
.UsedRange.Value = .UsedRange.Value
End With

then up to you to save your new workbook; the original I think will be
un-modified.

--Clif

 

[GS]

mp presented the following explanation :

the only problem is I have to be very careful to close
the master file without saving as the formulas have been overwritten
in the original

i'm working on a workaround for that
Sub CopyOverview()
With Sheets("overview")
.UsedRange.Value = .UsedRange.Value 'this destroys refs in original
.Copy 'opens a new wkb with a copy of the sheet and focus moves to new
wbk
End With

'save and close new workbook
ActiveWorkbook.SaveAs CurrentFolder & _
"\Overview.xls"
ActiveWorkbook.Close

'so far so good.
'but now i need to be sure *not* to save the original workbook
'I have to close without saving and reopen
'to get around that, somehow go back to original and undo the "pasting" of
values

'get back to original and undo overwritting of values on top of formulas
OriginalWorkbook.Activate
Call UndoPasteValues(ActiveWorkbook) '<<<< how to do this?

End Sub

Actually, just close the workbook without saving. No need to undo
anything here.

To make this easier to manage, set object vars for each workbook
something like:

Dim wkbSource As Workbook, wkbTarget As Workbook

Set wkbSource = ActiveWorkbook
With wkbSource.Sheets("overview")
.UsedRange.Value = .UsedRange.Value
set wkbTarget = .Copy
End with
wkbSource.Close False

wkbTarget.SaveAs CurrentFolder & "\Overview.xls"
wkbTarget.Close

 

[mp]

Untried ... perhaps something like this:

With Sheets("overview")
.Copy 'opens a new wkb with a copy of the sheet
End With
' does this make the activesheet the new copy?
' if so, then

With activesheet
.UsedRange.Value = .UsedRange.Value
End With

then up to you to save your new workbook; the original I think will be
un-modified.

--Clif

Thanks Clif
i thought of that but my thinking was that
since focus does move to new document,
and since code was started in first document
that code would not continue after first document had lost focus...
but i'll give it a try
thanks
mark

 

[mp]

also, since new wkbk won't have the sheets referred to by the
functions/references -
the values should all look like #ref
havent' tried yet, just thinking logically
mark

 

[Clif McIrvin]

Thanks Clif
i thought of that but my thinking was that
since focus does move to new document,
and since code was started in first document
that code would not continue after first document had lost focus...
but i'll give it a try
thanks
mark

The code continues ... in fact, as far as VBA is concerned the code and
the worksheet are not even related ... they may be, or they may not be
..... which is why eventually the programmer needs to learn
disambiguation .... which I'm still learning <grin>.

ThisWorkbook refers to the workbook the code is running in,
ActiveWorkbook refers to the workbook with focus. Take note of Garry's
reference to using objects to refer to the two different workbooks in
his reply.

 

[mp]

The code continues ... in fact, as far as VBA is concerned the code and
the worksheet are not even related ... they may be, or they may not be
.... which is why eventually the programmer needs to learn disambiguation
.... which I'm still learning <grin>.

ThisWorkbook refers to the workbook the code is running in, ActiveWorkbook
refers to the workbook with focus. Take note of Garry's reference to
using objects to refer to the two different workbooks in his reply.

sure enough, the code does continue
and what's more, even though the referenced sheets don't exist
in the new workbook, apparently the references must have automatically
gotten the source workbook name appended, because after updating the values
renaming, saving, closing and reopening the new workbook the values are
there intact!
Thanks a bunch
mark
Sub CopyOverview()
Dim CurrentFolder As String
CurrentFolder = ActiveWorkbook.Path
Dim oWs As Worksheet
'set ref to source
Set oWs = Sheets("overview")
With oWs
.Copy 'opens a new wkb with a copy of the sheet<< Thanks GS!!
End With

'set ref to new book before overwriting values '<< Thanks Cliff!!
Set oWs = ActiveWorkbook.ActiveSheet
With oWs
.UsedRange.Value = .UsedRange.Value
End With

'rename with todays date
ActiveWorkbook.SaveAs CurrentFolder & _
"\Overview-" & _
FileNameDateString(IncludeNone) & _
".xls"
ActiveWorkbook.Close

'now i'm back in orig book and formulas are intact
End Sub

(where FileNameDateString(IncludeNone) is a function returning string
version of current date usable in filename (replaces "\" with "-")
and Include is an enum determining whether to include hours, mins, secs or
none)

 

[GS]

mp has brought this to us :

Sub CopyOverview()
Dim CurrentFolder As String
CurrentFolder = ActiveWorkbook.Path
Dim oWs As Worksheet
'set ref to source
Set oWs = Sheets("overview")
With oWs
.Copy 'opens a new wkb with a copy of the sheet<< Thanks GS!!
End With

'set ref to new book before overwriting values '<< Thanks Cliff!!
Set oWs = ActiveWorkbook.ActiveSheet
With oWs
.UsedRange.Value = .UsedRange.Value
End With

'rename with todays date
ActiveWorkbook.SaveAs CurrentFolder & _
"\Overview-" & _
FileNameDateString(IncludeNone) & _
".xls"
ActiveWorkbook.Close

'now i'm back in orig book and formulas are intact
End Sub

The reason I explicitly used 2 wkb objects is so you don't have to work
with active objects, thus you can code logically without ambiguity to
what you're working on whether it's active or not. Many people fall
into the habit of selecting/activating everything before working on it.
That's just not efficient programming AND leaves your code wide open to
anything going wrong. It's only good programming practice to use fully
qualified object refs.<g>

 

[GS]

Just to clean it up some, still using fully qualified object refs AND
making it a bit more self-documenting:

Sub CopyOverview()
Dim wksSource As Worksheet, wkbTarget As Workbook
Dim SaveAsfilename As String
SaveAsfilename = ActiveWorkbook.Path &"\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"
Set wksSource = Sheets("overview")
Set wkbTarget = wksSource.Copy
With wkbTarget.Sheets("overview")
.UsedRange.Value = .UsedRange.Value
.SaveAs SaveAsFilename: wkbTarget.Close
End With
End Sub

 

[GS]

After serious thinking GS wrote :

Just to clean it up some, still using fully qualified object refs AND making
it a bit more self-documenting:

Sub CopyOverview()
Dim wksSource As Worksheet, wkbTarget As Workbook Dim SaveAsFilename As String
SaveAsfilename = ActiveWorkbook.Path &"\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"
Set wksSource = Sheets("overview")
Set wkbTarget = wksSource.Copy
With wkbTarget.Sheets("overview")
.UsedRange.Value = .UsedRange.Value .SaveAs SaveAsFilename: .Close
End With
End Sub

 

[GS]

Geez.., I think I need some sleep!

Sub CopyOverview()
Dim wksSource As Worksheet, wkbTarget As Workbook
Dim SaveAsfilename As String
SaveAsfilename = ActiveWorkbook.Path &"\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"
Set wksSource = Sheets("overview")
Set wkbTarget = wksSource.Copy

With wkbTarget
With .Sheets("overview")
.UsedRange.Value = .UsedRange.Value
End With
.SaveAs SaveAsFilename: .Close

 

[mp]

Geez.., I think I need some sleep!

With wkbTarget
With .Sheets("overview")
.UsedRange.Value = .UsedRange.Value
End With
.SaveAs SaveAsFilename: .Close

very nice, Thanks much...
now go get some sleep

mark

 

[Clif McIrvin]

very nice, Thanks much...
now go get some sleep

mark

I did

Garry gave you a *nice* clean-up, there. I keep forgetting that you can
do things like

Set wkbTarget = wksSource.Copy

That is a *really* useful technique. In fact, if you use the macro
recorder to get a starting point for you code, you can often take the
..Activate or .Select statements the recorder generates and change them
into a Set (YourObj) = ... and drop the .Select or .Activate; or, as
Garry suggested, combine the Set and the action into the same statement.

 

[GS]

You might want to know that in this scenario wksSource wasn't really
necessary since we could do this:

Set wkbTarget = Sheets("overview").Copy

...then do whatever with wkbTarget.

My point for using wksSource is that in some cases the formula refs may
result in unexpected behavior when copied to a new wkb and so may
require copying to the source wkb, convert formula results to constant
values, then Move the sheet into wkbTarget. So then...

Sub CopyOverview()
Dim wksSource As Worksheet, wkbTarget As Workbook
Dim SaveAsFilename As String, sWksName As String

SaveAsFilename = ThisWorkbook.Path & "\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"
sWksName = Sheets("overview").Name

Set wksSource = Sheets("overview").Copy After:=Sheets("overview")
With wksSource
.UsedRange.Value = .UsedRange.Value
End With

Set wkbTarget = wksSource.Move
With wkbTarget
.Sheets(1).Name = sWksName
.SaveAs SaveAsFilename: .Close
End With
End Sub

After which you're returned to the source wkb which should be closed
without saving changes. If you're done with it after running the
CopyOverview procedure then you can incorporate that into the process.

Add var:
Dim wkbSource As Workbook
Set wkbSource = ActiveWorkbook

At the end of the proc:
wkbSource.Close SaveChanges:=False

 

[mp]

maybe that will solve the current problem...

You might want to know that in this scenario wksSource wasn't really
necessary since we could do this:

Set wkbTarget = Sheets("overview").Copy

..then do whatever with wkbTarget.

My point for using wksSource is that in some cases the formula refs may
result in unexpected behavior when copied to a new wkb and so may require
copying to the source wkb, convert formula results to constant values,
then Move the sheet into wkbTarget. So then...

Sub CopyOverview()
Dim wksSource As Worksheet, wkbTarget As Workbook
Dim SaveAsFilename As String, sWksName As String

SaveAsFilename = ThisWorkbook.Path & "\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"
sWksName = Sheets("overview").Name

Set wksSource = Sheets("overview").Copy After:=Sheets("overview")
With wksSource
.UsedRange.Value = .UsedRange.Value
End With

Set wkbTarget = wksSource.Move
With wkbTarget
.Sheets(1).Name = sWksName
.SaveAs SaveAsFilename: .Close
End With
End Sub

After which you're returned to the source wkb which should be closed
without saving changes. If you're done with it after running the
CopyOverview procedure then you can incorporate that into the process.

Add var:
Dim wkbSource As Workbook
Set wkbSource = ActiveWorkbook

At the end of the proc:
wkbSource.Close SaveChanges:=False

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

i'll try the move version...
for some reason I'm getting an error at line 7
Sub CopyOverview()
'thanks GS from Excel programming ng (Garry)
'2-18-2011

Dim ProcName As String
1 ProcName = MODULE_NAME & "-CopyOverview"

2 LogIn ProcName, True

3 On Error GoTo CopyOverview_Error

Dim bSuccess As Boolean
Dim wksSource As Worksheet, wkbTarget As Workbook
Dim SaveAsFilename As String
4 SaveAsFilename = ActiveWorkbook.Path & "\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"
5 Set wksSource = Sheets("overview")
6 LogEntry "Got source sheet ", wksSource.Name

'vbErr <424> <VBAProject> (Object required)
'Linenumber: 7
' Set wkbTarget = wksSource.Copy
7 Set wkbTarget = Sheets("overview").Copy
8 With wkbTarget
9 With .Sheets("overview")
10 .UsedRange.Value = .UsedRange.Value
11 End With
12 .SaveAs SaveAsFilename: .Close
13 End With

I was sure it worked once, now something's changed
maybe something else somewhere else in code, but everything up to this point
seems to work ok....i just get stopped when calling this sub at line7
above...

something weirds going on,
i tried the move version above and get the compile error:
"expected function or variable" at the line
Set wkbTarget = wksSource.Move

I was getting that compile error previously on the line
Set wkbTarget = wksSource.Copy

also i had to wrap the arg in parens thusly:
Set wksSource = Sheets("overview").Copy (After:=Sheets("overview"))

 

[GS]

Actually, I never tried that this Copy method would return a wkb
object. Apparently it doesn't, which means I once again fell victim to
ass-u-me! Sorry, -my bad!

Here's a revision for both approaches, which have been tested:

Sub CopyOverview2()
' This copies a sheet containing formulas to a new wkb,
' converts the formula results to constant values on the copy,
' saves & closes the new wkb,
' and closes the source wkb without saving changes.

Dim wkbSource As Workbook, SaveAsFilename As String

SaveAsFilename = ActiveWorkbook.Path & "\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"

Sheets("overview").Copy
With ActiveSheet
.UsedRange.Value = .UsedRange.Value
End With
With ActiveWorkbook
.SaveAs SaveAsFilename: .Close
End With
wkbSource.Close SaveChanges:=False
End Sub

Sub MoveOverview2()
' This copies a sheet containing formulas,
' converts the formula results to constant values on the copy,
' moves the copy to a new wkb, saves & closes it,
' and closes the source wkb without saving changes.

Dim wkbSource As Workbook, SaveAsFilename As String

SaveAsFilename = ActiveWorkbook.Path & "\Overview-" _
& FileNameDateString(IncludeNone) & ".xls"

Set wkbSource = ActiveWorkbook
With wkbSource
.Sheets("overview").Copy after:=.Sheets("overview")
End With
With ActiveSheet
.UsedRange.Value = .UsedRange.Value
.Move
End With
With ActiveWorkbook
.SaveAs SaveAsFilename: .Close
End With
wkbSource.Close SaveChanges:=False
End Sub