Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
I tend to agree with Divakar. But in case you'd still want to go with the
original request, here's the macro
Sub CopyShapesAcross()
Dim shp As Shape
Dim lngLoop As Long
Application.ScreenUpdating = False
Set shp = Sheets(1).Shapes("NameOfShape")
For lngLoop = 2 To Sheets.Count
If Sheets(lngLoop).Type = xlWorksheet Then
shp.Copy
Sheets(lngLoop).Paste
Application.Goto Sheets(lngLoop).Cells(1)
With Sheets(lngLoop).Shapes(shp.Name)
.Top = shp.Top
.Left = shp.Left
End With
End If
Next lngLoop
With Application
.Goto Sheets(1).Cells(1)
.ScreenUpdating = True
End With
End Sub
Regards,
Sam Mathai Chacko (GL)
On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey wrote:
> it is so easy, if you use hyperlink instead of button
>
> Divaker
>
>
>
> On Wed, Oct 12, 2011 at 5:15 PM, Eddie wrote:
>
>> Hi,
>>
>> I have a workbook with around 100 worksheets. The first sheet acts as
>> a summary/introductory or front end and has links to other "sub-
>> sheets". In the first sub-sheet I've created a button containing a
>> macro that returns you to the introduction sheet. What I'd like to do
>> is copy this button onto each of the sub-sheets, however Excel doesnt
>> allow me to paste into multiple sheets. Is there anyway of replicating
>> this button in multiple sheets without having to do this manually for
>> each sheet?
>>
>> Any help very much appreciated.
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Sam Mathai Chacko
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
please Ignore previouse Mail
Sub MakeMyIndex()
Dim intICounter As Integer
Dim intTotalSheet As Integer
Dim wksIndexAs Worksheet
Dim shButtonAs Shape
Set wksIndex = Worksheets.Add
wksIndex.Name = "Index"
intTotalSheet = ThisWorkbook.Worksheets.Count
For intICounter = 1 To intTotalSheet
If Worksheets(intICounter).Name <> wksIndex.Name Then
Set shButton = wksIndex.Shapes.AddShape(msoShapeRectangle,
wksIndex.Cells(intICounter, 3).Left, wksIndex.Cells(intICounter, 3).Top,
wksIndex.Cells(intICounter, 3).Width, wksIndex.Cells(intICounter, 3).Height)
wksIndex.Hyperlinks.Add shButton, "", "'" &
Worksheets(intICounter).Name & "'!A1", "Click Me",
Worksheets(intICounter).Name
shButton.TextFrame.Characters.Text =
Worksheets(intICounter).Name
Set sh = Nothing
End If
Next
wksIndex.Cells(, 3).EntireColumn.ColumnWidth = 16
For intICounter = 1 To intTotalSheet
If Worksheets(intICounter).Name <> wksIndex.Name Then
Set shButton =
Worksheets(intICounter).Shapes.AddShape(msoShapeRectangle,
Worksheets(intICounter).Range("B3").Left,
Worksheets(intICounter).Range("B3").Top, _
Worksheets(intICounter).Range("B3").Width,
Worksheets(intICounter).Range("B3").Height)
Worksheets(intICounter).Columns(3).ColumnWidth = 16
wksIndex.Hyperlinks.Add shButton, "", "'" & wksIndex.Name &
"'!A1", "Click Me", "Index"
shButton.TextFrame.Characters.Text = "Index"
Set sh = Nothing
End If
Next
End Sub
On Wed, Oct 12, 2011 at 9:26 PM, rajan verma wrote:
> I Create a macro to an Index Sheet..
> Hope it will be Usable
>
> Sub MakeComponentinRunTImeOnSpreadSheet()
> On Error Resume Next
> Dim cb As OLEObject
> n = 5
> For i = 1 To 10
> Set cb = ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1")
> With cb
> .Top = n
> .Object.Caption = "Do you want to Select Range"
> .LinkedCell = Active.Offset(0, 5).Address
> .Object.Value = False
> .Visible = True
> End With
> ActiveCell.Offset(1, 0).Activate
> Set cb = Nothing
> n = n + 20
> Next
> End Sub
> Sub MakeMyIndex()
>
> Dim intICounter As Integer
> Dim intTotalSheet As Integer
> Dim wksIndexAs Worksheet
> Dim shButtonAs Shape
>
> Set wksIndex = Worksheets.Add
> wksIndex.Name = "Index"
> intTotalSheet = ThisWorkbook.Worksheets.Count
> For intICounter = 1 To intTotalSheet
> If Worksheets(intICounter).Name <> wksIndex.Name Then
> Set shButton = wksIndex.Shapes.AddShape(msoShapeRectangle,
> wksIndex.Cells(intICounter, 3).Left, wksIndex.Cells(intICounter, 3).Top,
> wksIndex.Cells(intICounter, 3).Width, wksIndex.Cells(intICounter, 3).Height)
> wksIndex.Hyperlinks.Add shButton, "", "'" &
> Worksheets(intICounter).Name & "'!A1", "Click Me",
> Worksheets(intICounter).Name
> shButton.TextFrame.Characters.Text =
> Worksheets(intICounter).Name
> Set sh = Nothing
> End If
> Next
> wksIndex.Cells(, 3).EntireColumn.ColumnWidth = 16
> For intICounter = 1 To intTotalSheet
> If Worksheets(intICounter).Name <> wksIndex.Name Then
> Set shButton =
> Worksheets(intICounter).Shapes.AddShape(msoShapeRectangle,
> Worksheets(intICounter).Range("B3").Left,
> Worksheets(intICounter).Range("B3").Top, _
> Worksheets(intICounter).Range("B3").Width,
> Worksheets(intICounter).Range("B3").Height)
> Worksheets(intICounter).Columns(3).ColumnWidth = 16
> wksIndex.Hyperlinks.Add shButton, "", "'" & wksIndex.Name &
> "'!A1", "Click Me", "Index"
> shButton.TextFrame.Characters.Text = "Index"
> Set sh = Nothing
> End If
> Next
> End Sub
>
>
>
> On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey wrote:
>
>> it is so easy, if you use hyperlink instead of button
>>
>> Divaker
>>
>>
>>
>> On Wed, Oct 12, 2011 at 5:15 PM, Eddie wrote:
>>
>>> Hi,
>>>
>>> I have a workbook with around 100 worksheets. The first sheet acts as
>>> a summary/introductory or front end and has links to other "sub-
>>> sheets". In the first sub-sheet I've created a button containing a
>>> macro that returns you to the introduction sheet. What I'd like to do
>>> is copy this button onto each of the sub-sheets, however Excel doesnt
>>> allow me to paste into multiple sheets. Is there anyway of replicating
>>> this button in multiple sheets without having to do this manually for
>>> each sheet?
>>>
>>> Any help very much appreciated.
>>>
>>> --
>>>
>>>
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
I Create a macro to an Index Sheet..
Hope it will be Usable
Sub MakeComponentinRunTImeOnSpreadSheet()
On Error Resume Next
Dim cb As OLEObject
n = 5
For i = 1 To 10
Set cb = ActiveSheet.OLEObjects.Add(classtype:="Forms.Checkbox.1")
With cb
.Top = n
.Object.Caption = "Do you want to Select Range"
.LinkedCell = Active.Offset(0, 5).Address
.Object.Value = False
.Visible = True
End With
ActiveCell.Offset(1, 0).Activate
Set cb = Nothing
n = n + 20
Next
End Sub
Sub MakeMyIndex()
Dim intICounter As Integer
Dim intTotalSheet As Integer
Dim wksIndexAs Worksheet
Dim shButtonAs Shape
Set wksIndex = Worksheets.Add
wksIndex.Name = "Index"
intTotalSheet = ThisWorkbook.Worksheets.Count
For intICounter = 1 To intTotalSheet
If Worksheets(intICounter).Name <> wksIndex.Name Then
Set shButton = wksIndex.Shapes.AddShape(msoShapeRectangle,
wksIndex.Cells(intICounter, 3).Left, wksIndex.Cells(intICounter, 3).Top,
wksIndex.Cells(intICounter, 3).Width, wksIndex.Cells(intICounter, 3).Height)
wksIndex.Hyperlinks.Add shButton, "", "'" &
Worksheets(intICounter).Name & "'!A1", "Click Me",
Worksheets(intICounter).Name
shButton.TextFrame.Characters.Text =
Worksheets(intICounter).Name
Set sh = Nothing
End If
Next
wksIndex.Cells(, 3).EntireColumn.ColumnWidth = 16
For intICounter = 1 To intTotalSheet
If Worksheets(intICounter).Name <> wksIndex.Name Then
Set shButton =
Worksheets(intICounter).Shapes.AddShape(msoShapeRectangle,
Worksheets(intICounter).Range("B3").Left,
Worksheets(intICounter).Range("B3").Top, _
Worksheets(intICounter).Range("B3").Width,
Worksheets(intICounter).Range("B3").Height)
Worksheets(intICounter).Columns(3).ColumnWidth = 16
wksIndex.Hyperlinks.Add shButton, "", "'" & wksIndex.Name &
"'!A1", "Click Me", "Index"
shButton.TextFrame.Characters.Text = "Index"
Set sh = Nothing
End If
Next
End Sub
On Wed, Oct 12, 2011 at 9:03 PM, Divaker Pandey wrote:
> it is so easy, if you use hyperlink instead of button
>
> Divaker
>
>
>
> On Wed, Oct 12, 2011 at 5:15 PM, Eddie wrote:
>
>> Hi,
>>
>> I have a workbook with around 100 worksheets. The first sheet acts as
>> a summary/introductory or front end and has links to other "sub-
>> sheets". In the first sub-sheet I've created a button containing a
>> macro that returns you to the introduction sheet. What I'd like to do
>> is copy this button onto each of the sub-sheets, however Excel doesnt
>> allow me to paste into multiple sheets. Is there anyway of replicating
>> this button in multiple sheets without having to do this manually for
>> each sheet?
>>
>> Any help very much appreciated.
>>
>> --
>>
>> --
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>> http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/discussexcel
>>
>
> --
>
> --
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Regards
Rajan verma
+91 9158998701
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to exce
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
it is so easy, if you use hyperlink instead of button Divaker On Wed, Oct 12, 2011 at 5:15 PM, Eddie wrote: > Hi, > > I have a workbook with around 100 worksheets. The first sheet acts as > a summary/introductory or front end and has links to other "sub- > sheets". In the first sub-sheet I've created a button containing a > macro that returns you to the introduction sheet. What I'd like to do > is copy this button onto each of the sub-sheets, however Excel doesnt > allow me to paste into multiple sheets. Is there anyway of replicating > this button in multiple sheets without having to do this manually for > each sheet? > > Any help very much appreciated. > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Pasting a button onto multiple sheets
Put this macro in the THISWORKBOOK module. Now when you double click cell a1
in any sheet you will go to sheet1
Change name and cell to suit
Private Sub Workbook_SheetBeforeDoubleClick _
(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
If Target = Sh.Range("a1") Then Sheets("sheet1").Select
End Sub
Don Guillett
SalesAid Software
dguille...@gmail.com
-Original Message-
From: Eddie
Sent: Wednesday, October 12, 2011 6:45 AM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Pasting a button onto multiple sheets
Hi,
I have a workbook with around 100 worksheets. The first sheet acts as
a summary/introductory or front end and has links to other "sub-
sheets". In the first sub-sheet I've created a button containing a
macro that returns you to the introduction sheet. What I'd like to do
is copy this button onto each of the sub-sheets, however Excel doesnt
allow me to paste into multiple sheets. Is there anyway of replicating
this button in multiple sheets without having to do this manually for
each sheet?
Any help very much appreciated.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ Pasting a button onto multiple sheets
Hi, I have a workbook with around 100 worksheets. The first sheet acts as a summary/introductory or front end and has links to other "sub- sheets". In the first sub-sheet I've created a button containing a macro that returns you to the introduction sheet. What I'd like to do is copy this button onto each of the sub-sheets, however Excel doesnt allow me to paste into multiple sheets. Is there anyway of replicating this button in multiple sheets without having to do this manually for each sheet? Any help very much appreciated. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
