Macro button

[Niko_Nick]

Hello,

I'd like to place a macro button on sheet1 but the macro should run/perform on sheet2. What do I need to do in VBA to make this happen? I'm relatively new to VBA so not sure where I would put my sheet reference. Here is my current VBA code.

If you need any other details, please let me know.

Sub Avid_GL()
'
' Avid_GL Macro
'


'
Cells.Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
End With
Selection.UnMerge
Selection.ColumnWidth = 15.14
Rows("1:8").Select
Selection.Delete Shift:=xlUp
Columns("B:D").Select
Selection.Delete Shift:=xlToLeft
Range("C:C,E:E,F:F,H:H,I:I,K:K").Select
Range("K1").Activate
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Delete Shift:=xlToLeft
Range("H2").Select
Selection.End(xlToRight).Select
Selection.End(xlToLeft).Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
End Sub

 

[Chrisdontm]

I am a basic VBA user as well...

What I would do is record a small macro starting on the page you would like to start on...

Then record the macro clicking over to another tab and land in a square on that page...

Then stop the macro, pull it up as though you wanted to edit it, and copy only the couple of lines that selected the the other page, and paste it into your main macro...
Then no matter where you start from you will go over to that page first thing...
i am sure there is a smoother way to do it, but this will work...
Also, when experimenting like this it is best to make a copy of the document to work with in case something goes wrong...

 

[My Aswer Is This]

Try this: See red text to see change:

Sub Avid_GL()
 '
 ' Avid_GL Macro
 '


 '
[COLOR="#FF0000"] Sheets("Sheet2").Activate[/COLOR]
 Cells.Select
 With Selection
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 End With
 Selection.UnMerge
 Selection.ColumnWidth = 15.14
 Rows("1:8").Select
 Selection.Delete Shift:=xlUp
 Columns("B:D").Select
 Selection.Delete Shift:=xlToLeft
 Range("C:C,E:E,F:F,H:H,I:I,K:K").Select
 Range("K1").Activate
 Selection.Delete Shift:=xlToLeft
 Columns("G:H").Select
 Selection.Delete Shift:=xlToLeft
 Range("H2").Select
 Selection.End(xlToRight).Select
 Selection.End(xlToLeft).Select
 Range("A2").Select
 Range(Selection, Selection.End(xlDown)).Select
 Range(Selection, Selection.End(xlToRight)).Select
 Selection.Copy
 [COLOR="#FF0000"]Sheets("Sheet1").Activate[/COLOR]
 End Sub

 

[Niko_Nick]

Thanks, I added the red highlights as suggested and it worked like a champ!!!

Thank You!!

Try this: See red text to see change:

Sub Avid_GL()
 '
 ' Avid_GL Macro
 '


 '
[COLOR=#FF0000] Sheets("Sheet2").Activate[/COLOR]
 Cells.Select
 With Selection
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 End With
 Selection.UnMerge
 Selection.ColumnWidth = 15.14
 Rows("1:8").Select
 Selection.Delete Shift:=xlUp
 Columns("B:D").Select
 Selection.Delete Shift:=xlToLeft
 Range("C:C,E:E,F:F,H:H,I:I,K:K").Select
 Range("K1").Activate
 Selection.Delete Shift:=xlToLeft
 Columns("G:H").Select
 Selection.Delete Shift:=xlToLeft
 Range("H2").Select
 Selection.End(xlToRight).Select
 Selection.End(xlToLeft).Select
 Range("A2").Select
 Range(Selection, Selection.End(xlDown)).Select
 Range(Selection, Selection.End(xlToRight)).Select
 Selection.Copy
 [COLOR=#FF0000]Sheets("Sheet1").Activate[/COLOR]
 End Sub

 

[My Aswer Is This]

Glad I could help. Come back here to Mr. Excel next time you need some assistance.

Thanks, I added the red highlights as suggested and it worked like a champ!!!

Thank You!!