CODEWITHSUNDEEP
vbaexcelms-office
Bluesector
Bluesector

Reputation: 329

Run a Macro on a different sheet using VBA (Excel)

I want to make a sheet that has different macro-buttons This sheet is named Buttons. The macro-buttons in this sheet are linked to macros that should run on different sheets. I tried to make a macro-button for the sheet 1. Stock & Demand:

Sub NeuerTag()

'Abfrage ob der Tag eingefügt werden soll, No = QUIT'
If MsgBox("Möchtest du die Tabelle vorbereiten?", vbYesNo) = vbNo Then Exit Sub

'Copies the last three coloumns of the Worksheet 1. Stock & Demand'
With Sheets("1. Stock & Demand")
Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
Columns(Lastcol - 1).Resize(, 1).Select
Selection.Copy

'Selects the first empty cell in 1. Stock & Demand and pastes'
Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-2, 1).Paste

'Pastes the Today()'
Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-1, 0).Select
Selection.Value = Date

'Paste Special - Values'
With Sheets("1. Stock & Demand")
Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
Columns(Lastcol - 3).Resize(, 1).PasteSpecial Paste:=xlPasteValues

End With
End With

End Sub

Now I have a Problem. Every time I make a macro-button and let it run it only does his Job in the sheet Buttons and not in the sheet I want it to work.

I have to say I'm not really good at coding so please explain it to me like im five ;-).

Upvotes: 0

Views: 2704

Answers (2)

OldUgly
OldUgly

Reputation: 2119

Something minor - inside your With statements, you are mis-coding. Take notice of the ".". By misplacing / omitting it, you will end up with results in the incorrect tab.

For example 

With Sheets("1. Stock & Demand")
    Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
    Columns(Lastcol - 3).Resize(, 1).PasteSpecial Paste:=xlPasteValues
End With

Should be

With Sheets("1. Stock & Demand")
    Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Columns(Lastcol - 3).Resize(, 1).PasteSpecial Paste:=xlPasteValues
End With

and this ...

With Sheets("1. Stock & Demand")
    Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
    Columns(Lastcol - 1).Resize(, 1).Select
    Selection.Copy

'Selects the first empty cell in 1. Stock & Demand and pastes'
    Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-2, 1).Paste

'Pastes the Today()'
    Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-1, 0).Select
    Selection.Value = Date

should be ...

With Sheets("1. Stock & Demand")
    Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    .Columns(Lastcol - 1).Resize(, 1).Copy

'Selects the first empty cell in 1. Stock & Demand and pastes'
    .Range("F3:ZZ3").End(xlToRight).Offset(-2, 1).Paste

'Pastes the Today()'
    .Range("F3:ZZ3").End(xlToRight).Offset(-1, 0).Value = Date

Upvotes: 1

Ivan Tokarev
Ivan Tokarev

Reputation: 101

You have to specify worksheetname for the macro. For example you can try this:

 Workbooks("Your_worksheet_name_here").Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-2, 1).Paste

Normally, to keep things clear i do something like this:

 Set targetSheet = Workbooks("Your_worksheet_name_here").Sheets("1. Stock & Demand")
 targetSheet.Range("F3:ZZ3").End(xlToRight).Offset(-2, 1).Paste

Upvotes: 1

Related Questions