| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Sheet to new Sheet and clear cells on original sheets
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
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). Boiler-Todd wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
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 "Boiler-Todd" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 "Boiler-Todd" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
Awesome Dave!
I think this will work. Thanks for the quick response. "Dave Peterson" wrote: 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). Boiler-Todd wrote: 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
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. "Dave Peterson" wrote: 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). Boiler-Todd wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Jacob Skaria" wrote: 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 "Boiler-Todd" wrote: 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Copy Sheet to new Sheet and clear cells on original sheets
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. Boiler-Todd wrote: 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. "Dave Peterson" wrote: 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). Boiler-Todd wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Links doesnt work in the copy version of the original sheet | Excel Discussion (Misc queries) | |||
| copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
| ???Replicate a sheet but chart still link to original sheet | Charts and Charting in Excel | |||
| in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
| Copy row sheet-sheet skipping unqualified cells | Excel Worksheet Functions | |||
Linear Mode
