Makro auf aktuelles Worksheet
18.12.2017 14:44:57
Daniel
ich generiere mit einem Befehl ein neues Tabellenblatt mit dem namen den ich eingebe:
Private Sub CB_2_Click()
If TB_1.Value "" Then
strNam = TB_1.Value
If strNam = "" Then Exit Sub
On Error Resume Next
Set wks = Worksheets(strNam)
If Err.Number 0 Then
Worksheets("Vorlage").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = strNam
Else
MsgBox ("Name existiert bereist")
End If
End If
End Sub
Jetzt meine Frage: wie kann ich einen Code den ich mit dem Makrorekorder aufgenommen habe immer für das aktuelle Tabellenblatt das ich generierte Tabellenblatt nutzen ?
hier der Code wenn es jemandem Hilft.
Private Sub CB_P_Click()
Sheets("Vorlage").Select 'wie kann ich immer das aktuelle Worksheet ansprechen ?
Range("J15:L17").Select
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M11").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J32:L34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M16").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J47:L49").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M21").Select
ActiveSheet.Paste
Range("B48").Select
Sheets("Vorlage").Select
Range("J54:L56").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M30").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J61:L62").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M26").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J86:L88").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H11").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J102:L107").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M35").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
ActiveWindow.SmallScroll Down:=42
Range("J112:L113").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("M43").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J124:L127").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H16").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J136:L140").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H22").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J148:L151").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H29").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J160:L164").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H35").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
ActiveWindow.SmallScroll Down:=36
Range("J169:L171").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H42").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J175:L176").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("H47").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J181:L183").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("C11").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J186:L188").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("C16").Select
ActiveSheet.Paste
Sheets("Vorlage").Select
Range("J191:L193").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PowerPointVorlage").Select
Range("C24").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-21
Range("Q8").Select
End Sub
Danke Gruß Daniel