copying sheets without references to the original workbook

[ardadogan]

hi,

i've been trying to find a solution to this problem but nothing i could find on the web seem to be able to solve it.

i'll simplify it as much as i can.

let's say i have two workbooks:

TEMPLATE.XLS with two sheets named
SHEET1 & SHEET2

123456.XLS again with two sheets named
SHEET1 & SHEET2

as 123456.XLS is made of the TEMPLATE.XLS, they have exactly same structures.

i want to replace SHEET1 in 123456.XLS with the SHEET1 in TEMPLATE.XLS.

it sounds like a very straightforward process: a macro to copy the sheet accross workbooks should do the trick BUT it doesn't. it does copy, but excel keeps forming references to TEMPLATE.XLS.

i know these references can be removed with a macro - but it's not only cells, i have plenty of buttons, combo boxes, radio boxes etc in the sheet so i just don't know how to have a reference-free sheet.


all i want is a one-to-one copy with no intervention from excel - as if i cut that part of the file and pasted it into the new one / i just want to transfer that EXACT sheet to the new workbook - it really shouldn't be that complicated.

is there a code line that'll just disable excel's supposedly smart reference forming thing?

or how else can i solve this?


i'd really appreciate any answer as i really need this desperately.

thank you so much,


arda

[CCkfm2000]

try this. found this on the web and i've modified it for my own use.


[vba]Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook

Set Source = Nothing
On Error Resume Next
Set Source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0



Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
On Error Resume Next[/vba]

[ardadogan]

thanks a lot for your reply, but this is not what i need. i need to keep my formulae and forms as they are - i want a one to one copy of the stupid sheet and for some reason microsoft has made it next to impossible to do it!

assume that you have a huge workbook and you do a mistake and copy-paste special all the values on one sheet. if you want to bring that one sheet from a backup you simply can't - all the formulas, form references etc will change.

just annoying.

anyway. still hoping someone's discovered a workaround for this.


arda

try this. found this on the web and i've modified it for my own use.


[vba]Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook

Set Source = Nothing
On Error Resume Next
Set Source = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0



Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
On Error Resume Next[/vba]

[ardadogan]

come on, has nobody had the same problem before? it's soooo frustrating, please post it if you have any ideas..

thanks!

thanks a lot for your reply, but this is not what i need. i need to keep my formulae and forms as they are - i want a one to one copy of the stupid sheet and for some reason microsoft has made it next to impossible to do it!

assume that you have a huge workbook and you do a mistake and copy-paste special all the values on one sheet. if you want to bring that one sheet from a backup you simply can't - all the formulas, form references etc will change.

just annoying.

anyway. still hoping someone's discovered a workaround for this.


arda