Copying Multiple Text Boxes

  • Thread starter Thread starter Ed Davis
  • Start date Start date
E

Ed Davis

I have 15 text boxes on a sheet.
I want to copy all 15 to all sheet names beginning with "01" and ending with
"31" which are days of the month.
I want to put them in the exact location where there are currently but in
the new sheet.
These sheets are already created and are setup exactly alike with the exact
same format.
I am using the following code to copy them now but it is very time consuming
changing the ranges and names.
Is there a way to just copy all text boxes to the next sheet and all the way
to the last sheet in the list "31".
Thank you so much in advance for any help given.


Sub CopyTextBox()
Sheets("01").Select
ActiveSheet.Shapes("TextBox 2").Select
Selection.Copy
Sheets("02").Select
Range("K1").Select
ActiveSheet.Paste
Range("A1").Select
End Sub
 
One way:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim MstrWks As Worksheet
Dim wks As Worksheet
Dim TB As TextBox
Dim NewTB As TextBox

Set MstrWks = Worksheets("01") '-- the worksheet with the correct tboxes.

For iCtr = 1 To 31
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
 
Thank you very much Dave
That worked great. Saves me a lot of time.
I have noted in my code that a lot of help came from people from this news
group.
If you or anyone else that has helped me from here has a website I would be
more than happy to include the address in the code. As well as include it in
my info page in the workbooks once it is complete.
Again thank you so much for your help.
 
That isn't necessary for me. I don't have a web site.

But this may be useful for you...

Search google groups for your posts. Then include a comment in your code with
the url for the thread. (It's nice for web sites, too.)

It used to be lots easier when google wasn't "fixed".





Ed said:
Thank you very much Dave
That worked great. Saves me a lot of time.
I have noted in my code that a lot of help came from people from this news
group.
If you or anyone else that has helped me from here has a website I would be
more than happy to include the address in the code. As well as include it in
my info page in the workbooks once it is complete.
Again thank you so much for your help.
Click to expand...
 
Back
Top