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

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

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.

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