Select Which Sheet to Import Data From

[cbzdmh]

Hi I'm trying to import data from a specific sheet in a workbook.

So far I have, it checks that the destination sheet is valid, once that has been done it gets a list of all the sheets from the source workbook and populates a list box in a user form.

I then can't select the sheet to copy the data off.

Sub Import_Flash()

'Imports a selected Page from a selected workbook
Dim SourceFileName As String
Dim TargetBook As String
Dim sheetexists As Boolean
Dim FlashImportSheet As String

'Checks for target sheet using Checkforsh, if it doesn't exist it creates it
   shtexists = CheckForSheet("Imported Flash")
    If Not shtexists Then
        Sheets.Add
        ActiveSheet.Name = "Imported Flash"
    Else
        Sheets("Imported Flash").Select
        Cells.Select
        Selection.ClearContents
    End If
    
TargetBook = ActiveWorkbook.Name
SourceFileName = Application.GetOpenFilename

If SourceFileName = "False" Then
        Exit Sub
ElseIf SourceFileName <> "" Then
        Workbooks.Open Filename:=SourceFileName
            
            With ActiveWorkbook
                For Each Sh In ActiveWorkbook.Worksheets
                    If Sh.Visible = True Then
                    FlashImport.FlashList.AddItem Sh.Name
                    End If
                Next Sh

'launches user form called FlashImport which lists all sheets in workbook
                FlashImport.Show

'
' THIS IS WHERE IT GOES WRONG, I CAN'T GET THE VARIABLE BACK FROM THE USERFORM 
'
                Sheets(FlashImportSheet).Select
                Cells.Select
                Selection.Copy
            End With
                
            Windows(TargetBook).Activate
            Range("A1").Select
            Selection.PasteSpecial Paste:=xlPasteValues
            'Cells.Select
            'Selection.RowHeight = 14
            'Cells.EntireColumn.AutoFit
           
End If
    
End Sub

The userform code

Sub FlashList_Change()
FlashImportSheet = FlashList.Value
FlashImport.Hide
End Sub

 

[cbzdmh]

I think I've just got to get the variable from the userform code back to the module, but i can't remember how.

 

[nirvana_]

If you use a command button to submit your data then once you submit the data run the remaining code to your requirements.