Copy complete sheet content into a given sheet, based on sheet name from combobox

[Hannes821]

Hello, everyone!

I am new to VBA,nice to meet you.
I have the following application plan:
1) there is Diagramme sheet on index number1 for visual Evaluation of processig data.
2) there is a "to be shown in sheet 1" sheet as data Input on sheet with index number2
3) the following Sheets are raw data of a process. to be able to analyse them and compare,
they get copied into sheet 2.Sheet 1 with table and Diagramm references to there,
you could say sheet 2 is my temporary Memory.

i partly solved this issue. what remains, is the copy job.
I am struggling with the active selection OR direct Name references;
Note that new sheets should be detected automatically after pasting from machines, which is why i use the index.

----------------------------------------------------------------------WORKBOOK CODE (Happy with that part so far, but open to feedback)

Private Sub Workbook_Open()

'1)überprüfe existierende Worksheets

'--------------------------Ansatz B--------------------------
Dim Mainworkbook As Workbook

Set Mainworkbook = ActiveWorkbook
'Print Mainworkbook.Sheets.Count

For Z = 1 To Mainworkbook.Sheets.Count
Mainworkbook.Sheets("Auswertung").Range("I" & Z) = Mainworkbook.Sheets(Z).Name
Next Z
'funktioniert, jedoch noch ohne Überschrift
Y = Mainworkbook.Sheets.Count - 2

MsgBox "Es wurden " & Y & " Ofenprofile erfasst.", vbOKOnly, "Erfasste Ofenprofile"


'Gib als Anzeige mehrere Zahlen entsprechend der Nummerierung zur Auswahl
'erlaube setzen der aktiven Zahl
'gib diese Zahl als identifikator des aktiven Worksheets aus ODER kopiere alle Daten in sheet Anzeige

'Lies alle Daten des gewählten Worksheets ein
Tabelle2.Anzeigeauswahl.Clear
Tabelle2.Anzeigeauswahl.Value = ""
With Tabelle2.Anzeigeauswahl 'erfasst dynamisch die eingefügten Tabellen
For L = 1 To Y
.AddItem Mainworkbook.Sheets(L + 2).Name
Next L

End With


'Tabelle2.Anzeigeauswahl.Value = Range("G5").Value

'If Tabelle2.Anzeigeauswahl.Value = Mainwork.Sheets(3).Name Then
'MsgBox "dekima****a"
'End If



End Sub


----------------------------------------------------------------------WORKSHEET 2 CODE (my internal temporary memory)

Private Sub Anzeigeauswahl_Change()

Dim ZeilenAnzahl As Long
Dim Anzeigeauswahl As String
Dim Mainworkbook As Workbook
Dim ProfilName As String
Dim rng1 As Range
Dim rng2 As Range


Set Mainworkbook = ActiveWorkbook
Anzeigeauswahl = Me.Anzeigeauswahl.Text
Application.ActiveSheet.Range("G4").Select
ActiveCell.Value = Anzeigeauswahl
MsgBox "Dieses Profil ist derzeit ausgewählt: " & Anzeigeauswahl, vbOKOnly, "Profilauswahl"

Select Case Ofenprofilsetzen

'Lade Ofenprofil 3 Tabelle 4 12-58
Case Anzeigeauswahl = Worksheets(3).Name
MsgBox Anzeigeauswahl & "Anzeigeauswahlll":
MsgBox Worksheets(3).Name & "Worksheet3 name: 58"
Set rng1 = Worksheets(3).Range("A1:T1000"):
Set rng2 = Worksheets(2).Range("A1:T1000"):
rng1.Copy rng2
'MsgBox "Profil 3" & Worksheets(3).Name

alteratively

''Lade Ofenprofil 5
' Case Anzeigeauswahl = Worksheets(5).Name
' Worksheets(5).UsedRange
' ZeilenAnzahl = Worksheets(5).UsedRange.Rows.Count
' ProfilName = Worksheets(5).Cells(2, 2).Value
' MsgBox "Ofenprofil 5 hat " & ZeilenAnzahl & " Zeilen.", vbOKOnly, ProfilName
' Worksheets(5).Range("A1:T913").Copy Destination:=Worksheets(2).Range("A1")


------------------------------------------------------------
so my Problem is that it does not Show the Sheets i want in the messageboxes and the copy is a mess.

Thanks, i appreciate any hints!