Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy all formats and objects
Currently I am using this code to copy Text Boxes to a group of worksheets.
What I would like to do is Remove any and all objects and formats including CF from the sheets first and then Copy Any and all Objects as well as Formats including CF from the master worksheet (MstrWks) to all the other sheets in the range. The objects may be TextBoxes or Command Buttons, The formats would be Cell width, row height, Cell colors, Number and text formats as well as any conditional formats. I received this code from here several months ago and after a few changes that I made it works perfectly except when I change a textbox I first have to remove all boxes from all the sheets except the mastersheet. Sub Copy_All_Text_Boxes() Dim iCtr As Long Dim MstrWks As Worksheet Dim wks As Worksheet Dim TB As TextBox Dim NewTB As TextBox Dim strSH As String Set MstrWks = Worksheets("01") '-- the worksheet with the correct Formats and objects. For iCtr = 1 To 33 Set wks = Nothing On Error Resume Next Set wks = Worksheets(Format(iCtr, "00")) On Error GoTo 0 If wks Is Nothing Then MsgBox "worksheets: " & Format(iCtr, "00") & " doesn't exist!" Else If wks.Name = MstrWks.Name Then 'skip it Else For Each TB In MstrWks.TextBoxes TB.Copy wks.Paste Set NewTB = wks.TextBoxes(wks.TextBoxes.Count) With NewTB .Top = TB.Top .Left = TB.Left 'these two probably aren't necessary .Width = TB.Width .Height = TB.Height End With Next TB End If End If Next iCtr End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy all formats and objects
It may be easier to just copy the master sheet as a whole.
Delete the existing sheet (01, 02, ...) and just copy that master sheet. You may have to tweak the new sheet (remove stuff you don't want, add a bit that you do), but it may turn out to be easier than doing all that other stuff. dim NewWks as worksheet dim iCtr as long set mstrwks = Worksheets("01") for ictr = 2 to 33 'skip the master sheet on error resume next application.displayalerts = false worksheets(format(ictr, "00")).delete application.displayalerts = true on error goto 0 mstrwks.copy _ after:=sheets(sheets.count) activesheet.name = format(ictr, "00") 'clean up anything that you don't want here next ictr (Uncompiled. Untested. Watch for typos.) Learning VBA wrote: Currently I am using this code to copy Text Boxes to a group of worksheets. What I would like to do is Remove any and all objects and formats including CF from the sheets first and then Copy Any and all Objects as well as Formats including CF from the master worksheet (MstrWks) to all the other sheets in the range. The objects may be TextBoxes or Command Buttons, The formats would be Cell width, row height, Cell colors, Number and text formats as well as any conditional formats. I received this code from here several months ago and after a few changes that I made it works perfectly except when I change a textbox I first have to remove all boxes from all the sheets except the mastersheet. Sub Copy_All_Text_Boxes() Dim iCtr As Long Dim MstrWks As Worksheet Dim wks As Worksheet Dim TB As TextBox Dim NewTB As TextBox Dim strSH As String Set MstrWks = Worksheets("01") '-- the worksheet with the correct Formats and objects. For iCtr = 1 To 33 Set wks = Nothing On Error Resume Next Set wks = Worksheets(Format(iCtr, "00")) On Error GoTo 0 If wks Is Nothing Then MsgBox "worksheets: " & Format(iCtr, "00") & " doesn't exist!" Else If wks.Name = MstrWks.Name Then 'skip it Else For Each TB In MstrWks.TextBoxes TB.Copy wks.Paste Set NewTB = wks.TextBoxes(wks.TextBoxes.Count) With NewTB .Top = TB.Top .Left = TB.Left 'these two probably aren't necessary .Width = TB.Width .Height = TB.Height End With Next TB End If End If Next iCtr End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy all formats and objects
The problem with doing that is that every sheet has formulas that reference
the sheet(s) before it. There are about 200 formulas on each sheet that calls data from previous sheet(s). I wouldn't think I would have to delete the formats except for the conditional formats, just the objects and a copy of the format would overwrite whatever formats that were set. "Dave Peterson" wrote in message ... It may be easier to just copy the master sheet as a whole. Delete the existing sheet (01, 02, ...) and just copy that master sheet. You may have to tweak the new sheet (remove stuff you don't want, add a bit that you do), but it may turn out to be easier than doing all that other stuff. dim NewWks as worksheet dim iCtr as long set mstrwks = Worksheets("01") for ictr = 2 to 33 'skip the master sheet on error resume next application.displayalerts = false worksheets(format(ictr, "00")).delete application.displayalerts = true on error goto 0 mstrwks.copy _ after:=sheets(sheets.count) activesheet.name = format(ictr, "00") 'clean up anything that you don't want here next ictr (Uncompiled. Untested. Watch for typos.) Learning VBA wrote: Currently I am using this code to copy Text Boxes to a group of worksheets. What I would like to do is Remove any and all objects and formats including CF from the sheets first and then Copy Any and all Objects as well as Formats including CF from the master worksheet (MstrWks) to all the other sheets in the range. The objects may be TextBoxes or Command Buttons, The formats would be Cell width, row height, Cell colors, Number and text formats as well as any conditional formats. I received this code from here several months ago and after a few changes that I made it works perfectly except when I change a textbox I first have to remove all boxes from all the sheets except the mastersheet. Sub Copy_All_Text_Boxes() Dim iCtr As Long Dim MstrWks As Worksheet Dim wks As Worksheet Dim TB As TextBox Dim NewTB As TextBox Dim strSH As String Set MstrWks = Worksheets("01") '-- the worksheet with the correct Formats and objects. For iCtr = 1 To 33 Set wks = Nothing On Error Resume Next Set wks = Worksheets(Format(iCtr, "00")) On Error GoTo 0 If wks Is Nothing Then MsgBox "worksheets: " & Format(iCtr, "00") & " doesn't exist!" Else If wks.Name = MstrWks.Name Then 'skip it Else For Each TB In MstrWks.TextBoxes TB.Copy wks.Paste Set NewTB = wks.TextBoxes(wks.TextBoxes.Count) With NewTB .Top = TB.Top .Left = TB.Left 'these two probably aren't necessary .Width = TB.Width .Height = TB.Height End With Next TB End If End If Next iCtr End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy all formats and objects
You can loop through shapes just like you looped through those textboxes.
Dim myShape as shape .... for each myshape in mstrwks.shapes As for the conditional formatting, I'd try recording a macro when I did it manually. Learning VBA wrote: The problem with doing that is that every sheet has formulas that reference the sheet(s) before it. There are about 200 formulas on each sheet that calls data from previous sheet(s). I wouldn't think I would have to delete the formats except for the conditional formats, just the objects and a copy of the format would overwrite whatever formats that were set. "Dave Peterson" wrote in message ... It may be easier to just copy the master sheet as a whole. Delete the existing sheet (01, 02, ...) and just copy that master sheet. You may have to tweak the new sheet (remove stuff you don't want, add a bit that you do), but it may turn out to be easier than doing all that other stuff. dim NewWks as worksheet dim iCtr as long set mstrwks = Worksheets("01") for ictr = 2 to 33 'skip the master sheet on error resume next application.displayalerts = false worksheets(format(ictr, "00")).delete application.displayalerts = true on error goto 0 mstrwks.copy _ after:=sheets(sheets.count) activesheet.name = format(ictr, "00") 'clean up anything that you don't want here next ictr (Uncompiled. Untested. Watch for typos.) Learning VBA wrote: Currently I am using this code to copy Text Boxes to a group of worksheets. What I would like to do is Remove any and all objects and formats including CF from the sheets first and then Copy Any and all Objects as well as Formats including CF from the master worksheet (MstrWks) to all the other sheets in the range. The objects may be TextBoxes or Command Buttons, The formats would be Cell width, row height, Cell colors, Number and text formats as well as any conditional formats. I received this code from here several months ago and after a few changes that I made it works perfectly except when I change a textbox I first have to remove all boxes from all the sheets except the mastersheet. Sub Copy_All_Text_Boxes() Dim iCtr As Long Dim MstrWks As Worksheet Dim wks As Worksheet Dim TB As TextBox Dim NewTB As TextBox Dim strSH As String Set MstrWks = Worksheets("01") '-- the worksheet with the correct Formats and objects. For iCtr = 1 To 33 Set wks = Nothing On Error Resume Next Set wks = Worksheets(Format(iCtr, "00")) On Error GoTo 0 If wks Is Nothing Then MsgBox "worksheets: " & Format(iCtr, "00") & " doesn't exist!" Else If wks.Name = MstrWks.Name Then 'skip it Else For Each TB In MstrWks.TextBoxes TB.Copy wks.Paste Set NewTB = wks.TextBoxes(wks.TextBoxes.Count) With NewTB .Top = TB.Top .Left = TB.Left 'these two probably aren't necessary .Width = TB.Width .Height = TB.Height End With Next TB End If End If Next iCtr End Sub -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy all formats and objects
ps. Shapes can be difficult to work with.
Ron de Bruin has a nice page about them: http://www.rondebruin.nl/controlsobjectsworksheet.htm Dave Peterson wrote: You can loop through shapes just like you looped through those textboxes. Dim myShape as shape ... for each myshape in mstrwks.shapes As for the conditional formatting, I'd try recording a macro when I did it manually. Learning VBA wrote: The problem with doing that is that every sheet has formulas that reference the sheet(s) before it. There are about 200 formulas on each sheet that calls data from previous sheet(s). I wouldn't think I would have to delete the formats except for the conditional formats, just the objects and a copy of the format would overwrite whatever formats that were set. "Dave Peterson" wrote in message ... It may be easier to just copy the master sheet as a whole. Delete the existing sheet (01, 02, ...) and just copy that master sheet. You may have to tweak the new sheet (remove stuff you don't want, add a bit that you do), but it may turn out to be easier than doing all that other stuff. dim NewWks as worksheet dim iCtr as long set mstrwks = Worksheets("01") for ictr = 2 to 33 'skip the master sheet on error resume next application.displayalerts = false worksheets(format(ictr, "00")).delete application.displayalerts = true on error goto 0 mstrwks.copy _ after:=sheets(sheets.count) activesheet.name = format(ictr, "00") 'clean up anything that you don't want here next ictr (Uncompiled. Untested. Watch for typos.) Learning VBA wrote: Currently I am using this code to copy Text Boxes to a group of worksheets. What I would like to do is Remove any and all objects and formats including CF from the sheets first and then Copy Any and all Objects as well as Formats including CF from the master worksheet (MstrWks) to all the other sheets in the range. The objects may be TextBoxes or Command Buttons, The formats would be Cell width, row height, Cell colors, Number and text formats as well as any conditional formats. I received this code from here several months ago and after a few changes that I made it works perfectly except when I change a textbox I first have to remove all boxes from all the sheets except the mastersheet. Sub Copy_All_Text_Boxes() Dim iCtr As Long Dim MstrWks As Worksheet Dim wks As Worksheet Dim TB As TextBox Dim NewTB As TextBox Dim strSH As String Set MstrWks = Worksheets("01") '-- the worksheet with the correct Formats and objects. For iCtr = 1 To 33 Set wks = Nothing On Error Resume Next Set wks = Worksheets(Format(iCtr, "00")) On Error GoTo 0 If wks Is Nothing Then MsgBox "worksheets: " & Format(iCtr, "00") & " doesn't exist!" Else If wks.Name = MstrWks.Name Then 'skip it Else For Each TB In MstrWks.TextBoxes TB.Copy wks.Paste Set NewTB = wks.TextBoxes(wks.TextBoxes.Count) With NewTB .Top = TB.Top .Left = TB.Left 'these two probably aren't necessary .Width = TB.Width .Height = TB.Height End With Next TB End If End If Next iCtr End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy all formats and objects
Thank you Dave
That helps me a lot. I am sure I can do it from here. Again Thanks "Dave Peterson" wrote in message ... ps. Shapes can be difficult to work with. Ron de Bruin has a nice page about them: http://www.rondebruin.nl/controlsobjectsworksheet.htm Dave Peterson wrote: You can loop through shapes just like you looped through those textboxes. Dim myShape as shape ... for each myshape in mstrwks.shapes As for the conditional formatting, I'd try recording a macro when I did it manually. Learning VBA wrote: The problem with doing that is that every sheet has formulas that reference the sheet(s) before it. There are about 200 formulas on each sheet that calls data from previous sheet(s). I wouldn't think I would have to delete the formats except for the conditional formats, just the objects and a copy of the format would overwrite whatever formats that were set. "Dave Peterson" wrote in message ... It may be easier to just copy the master sheet as a whole. Delete the existing sheet (01, 02, ...) and just copy that master sheet. You may have to tweak the new sheet (remove stuff you don't want, add a bit that you do), but it may turn out to be easier than doing all that other stuff. dim NewWks as worksheet dim iCtr as long set mstrwks = Worksheets("01") for ictr = 2 to 33 'skip the master sheet on error resume next application.displayalerts = false worksheets(format(ictr, "00")).delete application.displayalerts = true on error goto 0 mstrwks.copy _ after:=sheets(sheets.count) activesheet.name = format(ictr, "00") 'clean up anything that you don't want here next ictr (Uncompiled. Untested. Watch for typos.) Learning VBA wrote: Currently I am using this code to copy Text Boxes to a group of worksheets. What I would like to do is Remove any and all objects and formats including CF from the sheets first and then Copy Any and all Objects as well as Formats including CF from the master worksheet (MstrWks) to all the other sheets in the range. The objects may be TextBoxes or Command Buttons, The formats would be Cell width, row height, Cell colors, Number and text formats as well as any conditional formats. I received this code from here several months ago and after a few changes that I made it works perfectly except when I change a textbox I first have to remove all boxes from all the sheets except the mastersheet. Sub Copy_All_Text_Boxes() Dim iCtr As Long Dim MstrWks As Worksheet Dim wks As Worksheet Dim TB As TextBox Dim NewTB As TextBox Dim strSH As String Set MstrWks = Worksheets("01") '-- the worksheet with the correct Formats and objects. For iCtr = 1 To 33 Set wks = Nothing On Error Resume Next Set wks = Worksheets(Format(iCtr, "00")) On Error GoTo 0 If wks Is Nothing Then MsgBox "worksheets: " & Format(iCtr, "00") & " doesn't exist!" Else If wks.Name = MstrWks.Name Then 'skip it Else For Each TB In MstrWks.TextBoxes TB.Copy wks.Paste Set NewTB = wks.TextBoxes(wks.TextBoxes.Count) With NewTB .Top = TB.Top .Left = TB.Left 'these two probably aren't necessary .Width = TB.Width .Height = TB.Height End With Next TB End If End If Next iCtr End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy objects false | Excel Discussion (Misc queries) | |||
Copy Worksheet wont copy objects. | Excel Worksheet Functions | |||
how do I copy all cells and all objects in a sheet together? | Excel Discussion (Misc queries) | |||
how do I link objects with color formats | Excel Discussion (Misc queries) | |||
Copy a folder and objects | New Users to Excel |