How to copy textboxes from one sheet to another

February 11, 2018 at 05:23:22
Specs: Windows 10
Please, I would like to copy and paste a few textboxes from one sheet to a new one. The macro listed below works well in a new fresh workbook:
Sub Move_controls()
 Dim sh As Shape
 Dim T, L As Integer

 Sheets("MasterCopy").Select
     For Each sh In ActiveSheet.Shapes
     If sh.Type <> msoComment Then
         T = sh.Top
         L = sh.Left
         
         For Each Sheet In ActiveWorkbook.Sheets
            If Sheet.Name <> "MasterCopy" Then
                 sh.Copy
                    Sheet.Select
                        ActiveSheet.PasteSpecial Link:=False, DisplayAsIcon:=False
                sh.Select
                Selection.Top = T
                Selection.Left = L
         
            End If
         Next
     End If

     Sheets("MasterCopy").Select
     Next
 End Sub

Unfortunately it doesn't work when I try to nest it to a macro that opens automatically a new sheet by copying a sheet called MasterCopy sheet in to a new one.
Here you go the other macro that opens automatically a new sheet copying and pasting the MasterCopy sheet with all its features.

Private Sub Workbook_NewSheet(ByVal sh As Object)
    Dim tmpName As String

    tmpName = sh.Name
    Sheets("MasterCopy").Copy Before:=Sheets(sh.Name)
    Application.DisplayAlerts = False
    Sheets(sh.Name).Delete
    Application.DisplayAlerts = True
    Sheets("MasterCopy (2)").Name = tmpName
    
End Sub

Have you got any suggestion on how to nest these two codes and copy and paste MasterCopy sheet with all its textboxes and features?
Your help is really appreciated.
Kind Regards
Tommaso

message edited by TOMMASO


See More: How to copy textboxes from one sheet to another

Reply ↓  Report •

#1
February 11, 2018 at 17:10:56
To clarify your situation..

Are these Word documents or Excel sheets?


Reply ↓  Report •

#2
February 11, 2018 at 23:41:48
Those documents are Excel sheets. Thanks

message edited by TOMMASO


Reply ↓  Report •

#3
February 14, 2018 at 06:44:26
I know how to link a specific cell from one cell to another; in effect create a new sheet and have data arrive there from a previous sheet. But that is a cell by cell creation etc. and likely not what you seek?

The link below gives you a whole slew of possible solutions - and perhaps if there s one that works for your situation, you can post back with the details?

Such feed back is useful for all here; and anyone who may come across this post in the future via a web search?

http://tinyurl.com/y8boxxzq


Reply ↓  Report •

Related Solutions

#4
February 14, 2018 at 07:42:33
consider re-asking this under SOFTWARE --> OFFICE SOFTWARE
there are a few guys over there with lots of VBA/excel knowledge

i5-6600K[delid]@4.814GHz/4.613GHz cache@1.38v | 2x4GB Crucial-DDR4-2133@14-14-14-28 1T 2808MHz@1.37v
ASUS Z170K
Samsung 250GB SSD 850 EVO
MSI Armor RX 570 4GB@1380c/2087m BiosMod
VS450


Reply ↓  Report •

#5
February 14, 2018 at 07:51:19
excellent suggestion from "hidde663" (any relationship to 633 Squadron?)

Post (the same detailed info etc.) over there as more likely at least three of the Excel etc. cognoscenti will see it and help more fully.


Reply ↓  Report •

#6
February 14, 2018 at 07:58:05
nope, the name hidde was already taken so a random number was added

i5-6600K[delid]@4.814GHz/4.613GHz cache@1.38v | 2x4GB Crucial-DDR4-2133@14-14-14-28 1T 2808MHz@1.37v
ASUS Z170K
Samsung 250GB SSD 850 EVO
MSI Armor RX 570 4GB@1380c/2087m BiosMod
VS450


Reply ↓  Report •

#7
February 14, 2018 at 09:00:05
Well you chose a distinguished RAF Mosquito squadron number - well it existed in a British movie of that name (633 Squadron)...

Which having sed just realised you’re 663 not 633...

Obviously need more a frequent intake of tea ‘n cream cake to sustain grey cells...


Reply ↓  Report •

Ask Question