CODEWITHSUNDEEP
vbaexcelms-office
Bluesector
Bluesector

Reputation: 329

Use Excel Macro in a different Sheet

I want to create a Menu-sheet from where I can start different macros for different sheets. But the code doesn't work when i start is from a differet sheet. The code only works correctly when I start it from the "1. Stock & Demand" sheet

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

'Kopiert die letzten 3 Spalten des Tabellenblattes'
With Sheets("1. Stock & Demand")
Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
Columns(Lastcol - 1).Resize(, 1).Select
Selection.Copy

'Wählt die erste freie Spalte aus und fügt das zuvor kopierte ein'
Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-2, 1).Select
ActiveSheet.Paste

'Aktuelles Datum einfügen'
Sheets("1. Stock & Demand").Range("F3:ZZ3").End(xlToRight).Offset(-1, 0).Select
Selection.Value = Date

'Wählt die zuvor kopierten Spalten aus und nimmt die Formeln raus'
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

Thanks in advance & Greetings

Upvotes: 0

Views: 98

Answers (1)

John Coleman
John Coleman

Reputation: 51998

As indicated in the comments, there are at least two issues

1) When you say "I want to create a Home-Sheet from where I can start different macros for different sheets." you give the impression that you want to use the code module for "Home-Sheet" to contain the macros. Most macros don't belong in such sheet modules. Instead they belong in standard code modules. Insert one in the VBA editor and put it there.

2) It doesn't matter that a line like

Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column rather than Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

is within the scope of a With statement. Unless you prefix Cells with a dot, VBA will interpret the Cells in the context of the active sheet. Instead, use:

Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column rather than Lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

Similar remarks hold for Columns.

Upvotes: 3

Related Questions