delete sheets from a workbook without creating linked formulae
 

I have an Excel 2003 workbook that I wish to split in to two sections (3
sheets and 2 sheets).
The two sheets will remain as a 'reporting module' whilst the others will be
copied many times and re-used for data gather.
I need to be able to paste the data sheets back into the worbook with the
reporting module and the formulaes to remain correct.
Is there a way of doing this? As soon as I cut the data sheets out of the
original book, the formulae change to #REF and then don't work when the new
data is pasted back in.
I know that the incoming sheets need to keep their original name but is
there a simple method I can use?

Thanks

delete sheets from a workbook without creating linked formulae
 

I -think- I know what you are doing and I do basically the same thing on
one of my spreadsheets (use a separate tab for reporting that is). I
made a macro to create a tab, copy what I needed and then print and
delete the tab. Then I tied this macro to a button.

If I've misunderstood you, just ignore me and I'll go away :P


Code:
--------------------
Sub RK_Review()
'
' RK_Review Macro
' Macro recorded 8/15/2006 by Cberry
'
' Keyboard Shortcut: Ctrl+s
'
Dim sNewSheet As String
Sheets("Output").Select
Sheets.Add
sNewSheet = ActiveSheet.Name
Sheets("Output").Select
Columns("A:E").Select
Selection.Copy
Sheets(sNewSheet).Select
Range("a1").PasteSpecial xlValues
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Output").Select
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets(sNewSheet).Select
Columns("F:F").Select
Range("F1").PasteSpecial xlValues
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Output").Select
Columns("Z:AF").Select
Application.CutCopyMode = False
Selection.Copy
Sheets(sNewSheet).Select
Range("g1").PasteSpecial xlValues
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.PageSetup.PrintArea = "$A$2:$M$250"
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = "$A:$C"
End With
ActiveSheet.PageSetup.PrintArea = "$A$2:$M$250"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 70
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets(sNewSheet).Select
ActiveWindow.SelectedSheets.Delete
End Sub

--------------------


Yes, I know it's sloppy and probably inefficient, but it gets the job
done. Any body that can make this better, please let me know.


--
Chris Berry
------------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php...o&userid=36165
View this thread: http://www.excelforum.com/showthread...hreadid=574396

delete sheets from a workbook without creating linked formulae
 

You could just copy the sheets you want to distribute for data collection and
when you get updated worksheets back copy/paste the data (not the
spreadsheets themselves), back into the appropriate sheets of the original
workbook. That should not break any formulas as long as the structure of the
data does not change.

Alternately, you could put the 'data collection' sheets into a separate
workbook. To do this just drag the sheets out of the original workbook into a
new workbook, (the workbook window must not be maximized to do this) and save
it with a name of say, Data.xls. Your 'reporting module' formulas should now
have external links to the sheets in the Data.xls workbook, so now you should
save the 'reporting module' workbook.

Now you can distribute copies of the Data.xls file to collect new data, and
when you get updated sheets back just save them back over the original
Data.xls. (Do this while the 'reporting' workbook is closed). Then, when you
open the 'reporting module' it will refer to the new data.

Hope that does it for you,

TK

"BlueTig" wrote:

I have an Excel 2003 workbook that I wish to split in to two sections (3
sheets and 2 sheets).
The two sheets will remain as a 'reporting module' whilst the others will be
copied many times and re-used for data gather.
I need to be able to paste the data sheets back into the worbook with the
reporting module and the formulaes to remain correct.
Is there a way of doing this? As soon as I cut the data sheets out of the
original book, the formulae change to #REF and then don't work when the new
data is pasted back in.
I know that the incoming sheets need to keep their original name but is
there a simple method I can use?

Thanks

delete sheets from a workbook without creating linked formula
 

Thanks folks,

Two different but workable approaches to the same problem.

Many thanks,

BlueTig

"T Kirtley" wrote:

You could just copy the sheets you want to distribute for data collection and
when you get updated worksheets back copy/paste the data (not the
spreadsheets themselves), back into the appropriate sheets of the original
workbook. That should not break any formulas as long as the structure of the
data does not change.

Alternately, you could put the 'data collection' sheets into a separate
workbook. To do this just drag the sheets out of the original workbook into a
new workbook, (the workbook window must not be maximized to do this) and save
it with a name of say, Data.xls. Your 'reporting module' formulas should now
have external links to the sheets in the Data.xls workbook, so now you should
save the 'reporting module' workbook.

Now you can distribute copies of the Data.xls file to collect new data, and
when you get updated sheets back just save them back over the original
Data.xls. (Do this while the 'reporting' workbook is closed). Then, when you
open the 'reporting module' it will refer to the new data.

Hope that does it for you,

TK

"BlueTig" wrote:

I have an Excel 2003 workbook that I wish to split in to two sections (3
sheets and 2 sheets).
The two sheets will remain as a 'reporting module' whilst the others will be
copied many times and re-used for data gather.
I need to be able to paste the data sheets back into the worbook with the
reporting module and the formulaes to remain correct.
Is there a way of doing this? As soon as I cut the data sheets out of the
original book, the formulae change to #REF and then don't work when the new
data is pasted back in.
I know that the incoming sheets need to keep their original name but is
there a simple method I can use?

Thanks