Reputation: 329
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: 2702
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
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