copy and clear sheets with VBA

J

jlong

I have a template sheet that users fill in, this creates calculations
and a graph on another worksheet. I have a command button that copies
the worksheet and renames it and writes to a data bank. It also clears
the original template. However when it clears the template all copied
worksheets clear as well. what can I do ???
Please help
 
F

firefytr

Hi jlong,

Right off the top of my head it sounds like when you're copying th
worksheets, their linking and retaining those link. So when you clea
the 'master' they clear as well. could you post your code
 
D

Don

Hi Jlong,

I've got a similar workbook. A sheet is generated from
another data entry sheet, then copied to a new book and
saved with a unique filename.

The way I addressed what you're talking about was within
the macro right after the "Copy to New Book" is done, do a
select all, then copy, then paste special/value right over
the area you just copied. This will kill all the links
and reduce the new book to just values. (as an unexpected
bonus, it also reduced my file sizes by 2/3rd's) When the
new book is opened again it will still retain the original
information with no links.

HTH,

Don
 
J

jlong

I'm a little slow at this and still can't seem to get it to work, her
is my code. ANy suggestions??? Thankyou


Sub Copy_Sheet_and_Duties()
'******************************
'Copy Sheet
'******************************
Sheets("entry sheet").Select
With ActiveSheet
.Copy After:=Sheets(3)

End With


'**********************************
'Sheetname comes from time and date
'**********************************

Dim Sheetnew As String
Dim SheetDay
Dim SheetMonth
Dim SheetHour
Dim SheetMinute
Dim SheetCount


Dim Tyme
Dim Dyte
Dim MSort

'Heres where we grab the info from the form thats not visible but
'still loaded
'Tyme = Format(Time, "HH:MM")
'Dyte = Format(Date, "mm/dd/yy")
Dyte = FrmCaption.txtboxDate.Value
Tyme = FrmCaption.txtboxTime.Value
MSort = FrmCaption.txtMSSelected.Value


SheetCount = Worksheets.Count
SheetHour = Hour(Tyme)
SheetMinute = Minute(Tyme)
SheetMonth = Month(Dyte)
SheetDay = Day(Dyte)

If SheetHour < 10 Then
SheetHour = "0" & SheetHour
End If
If SheetMinute < 10 Then
SheetMinute = "0" & SheetMinute
End If
If SheetDay < 10 Then
SheetDay = "0" & SheetDay
End If
If SheetMonth < 10 Then
SheetMonth = "0" & SheetMonth
End If

Sheetnew = SheetMonth & "-" & SheetDay & " at " & SheetHour & "_"
SheetMinute & " " & MSort


Sheetnew = InputBox("New Sheet Name: ", "New sheet name ok?"
Sheetnew)


'Rename Sheet3to new name
Rename:
'Check if name exists, if so add a "I" to the name and rechec
etc..
If SheetExists(Sheetnew) = False Then
Sheets(4).Name = Sheetnew
Else
Sheetnew = Sheetnew & "I"
GoTo Rename
End I
 
J

jlong

My sheets also copies a graph, I think this might make things a bi
tougher but I'm not sure
 
Top