Copy Sheet to new Sheet and clear cells on original sheets

[Boiler-Todd]

I am new to using VBA so let me explain what I am trying to do...
I want to be able to copy 3 tabs(sheets) in a workbook into 3 new
tabs(sheets) into the same workbook. Also if the date is two days later then
I want to delete values on the orignal sheets. I also want to do this
everytime the sheet is opened.

My current code loops to copy each sheet three times and then deletes data
on the copied sheets but not the original sheets. See below. Thanks for all
help!

Sub Workbook_Open()
' This Macro will copy sheets 1-2-3 to new sheets A-B-C then
' if date is 2 days past clear out the UNLOCKED cells of a sheets (1-3)
'
Dim cell As Range
For Each sh In ActiveWorkbook.Sheets
If Date > sh.Range("A1").Value - 2 Then
Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(2).Copy After:=Sheets(Sheets.Count)
Sheets(3).Copy After:=Sheets(Sheets.Count)
For Each cell In Range("A1:H10")
If Not cell.Locked Then cell.ClearContents
Next cell
End If
Next
End Sub

 

[Dave Peterson]

Maybe...

Sub Workbook_Open()
dim iCtr as long
dim myCell as range

'copy the sheets each time--no matter what
Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(2).Copy After:=Sheets(Sheets.Count)
Sheets(3).Copy After:=Sheets(Sheets.Count)

'check to see if the original sheets should be cleared
for ictr = 1 to 3
with sheets(ictr)
If Date > .Range("A1").Value - 2 Then
For Each mycell In .Range("A1:H10").cells
If Not mycell.Locked Then
mycell.ClearContents
end if
Next mycell
End If
end with
Next ictr
End Sub

The with/end with structure means that all those objects with the leading dots
(.Ranges()'s) belong to the object in the previous with statement. In this
case, Sheets(ictr) (sheets(1), sheets(2) and sheets(3)).

I made some minor changes just because.

I like the multiline "if" statement better than the single line. I find it
easier to change and debug (lining up If's and End if's).

 

[Jacob Skaria]

Try the below

Dim cell As Range, intSheet As Integer

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(2).Copy After:=Sheets(Sheets.Count)
Sheets(3).Copy After:=Sheets(Sheets.Count)

For intSheet = 1 To 3
If Date > Sheets(intSheet).Range("A1").Value - 2 Then
For Each cell In Range("A1:H10")
If Not cell.Locked Then cell.ClearContents
Next cell
End If
Next

If this post helps click Yes

 

[Jacob Skaria]

Dim cell As Range, intSheet As Integer

Sheets(1).Copy After:=Sheets(Sheets.Count)
Sheets(2).Copy After:=Sheets(Sheets.Count)
Sheets(3).Copy After:=Sheets(Sheets.Count)

For intSheet = 1 To 3
If Date > Sheets(intSheet).Range("A1").Value - 2 Then
For Each cell In Sheets(intSheet).Range("A1:H10")
If Not cell.Locked Then cell.ClearContents
Next cell
End If
Next

If this post helps click Yes

 

[Boiler-Todd]

Awesome Dave!

I think this will work. Thanks for the quick response.

 

[Boiler-Todd]

Dave let me enhance this a step further. I have the orignal 3 sheets.
Everytime I open the workbook, I just want to copy the sheets into 3 other
sheets. I just want to overwrite the "copied sheets" each time I open the
workbook. I don't want a third or forth copy of the original sheet. So my
total number of sheet should always be six: Sheet1, Sheet2, Sheet3,
Sheet1(1), Sheet2(1), Sheet3(1).

Each time I open the workbook this macro makes another trio of sheets.

 

[Boiler-Todd]

Jacob,

This works fine but I would like a slight enhancement. Everytime I open the
workbook another trio of sheets are created. I would only want to have the
original three sheets and the three copies that are overwritten each time the
criteria is met.

What do I need to do to modify this?

Thanks, Todd.

 

[Dave Peterson]

If those sheets already exist, then use code like:

Sheets("Sheet1").cells.Copy _
destination:=Sheets("Sheets1 (1)").range("A1")

Repeat this twice more and fix the sheet names. Watch your typing. The names
in double quotes have to match exactly--spaces are important (if they're there).

If the 3 sheets that you're copying to don't exist, then just make copies
manually.

ps. I'd rename all your sheets to something mnemonically significant. Help the
user find the correct sheet based on its name.