[VBA] Import Sheet From File Path & Cell

[DataBlake]

I have a sheet macro on a sheet named "Program Start" that opens various userforms based on which cells are clicked as well as switches off the sheet. When the userform portion of the code is completed it returns the sheet selection back to Program Start.

If Target.Address = "$H$2" Then
   Sheets("TSS").Select
   TSS.Show
   Sheets("Program Start").Select
End If

If Target.Address = "$I$2" Then
   Sheets("W1").Select
   W1.Show
   Sheets("Program Start").Select
End If

If Target.Address = "$J$2" Then
   Sheets("RTH").Select
   RTH.Show
   Sheets("Program Start").Select
End If

I would like a file named the same thing as the cell (i.e J2 = RTH) in a specific folder:
C:\Users\user\Desktop\Excel Program\Update INV\Spec Sheets\
to be copied to the activesheet in the workbook with the import sub.

So lets say i doubleclick I2: it would select sheet W1
then import the data from the Spec Sheets folder named W1.xlsx (whatever the first sheet is)
so the end result would be

If Target.Address = "$H$2" Then
   Sheets("TSS").Select
   Call IMPORTats
   TSS.Show
   Sheets("Program Start").Select
End If

If Target.Address = "$I$2" Then
   Sheets("W1").Select
   Call IMPORTats
   W1.Show
   Sheets("Program Start").Select
End If

If Target.Address = "$J$2" Then
   Sheets("RTH").Select
   Call IMPORTats
   RTH.Show
   Sheets("Program Start").Select
End If

any help would be greatly appreciated because i can't quite nail this

 

[DataBlake]

i imagine you'd have to pass a string variable between the subs for the value of which cell you double click, but other than that i haven't the slightest about file dialogs

 

[DataBlake]

D	E	F	G	H	I	J
WP		MMH	WKM	TSS	W1	RTH

<tbody>
</tbody>

"Program Start" worksheet ^

I created a sub that works, but i really don't think its optimal code/compact wise (at least it doesn't take very long). So i double click WP i need it to assign the text string of the active cell + "-" + today's date. I then need it to open a file with that text string's value + .xlsx . it copies the first sheet over to the file with the macro, then shows a userform.

This is what my tiny peon brain can handle:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim activeWB As Workbook
    Dim openWB As Workbook
    Dim textVAR As String
    Dim filePATH As String
    Dim newSHEET As Worksheet
    
    Set activeWB = Application.ActiveWorkbook
    
    If Target.Address = "$D$2" Then
        textVAR = ActiveCell.Text
        filePATH = "C:\Users\USERNAME\Desktop\" & ActiveCell.Text & "\" & textVAR & "-" & Format(Now(), "ddmmyy")
        Set newSHEET = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(1))
        newSHEET.Name = textVAR
        Set openWB = Application.Workbooks.Open(filePATH)
        openWB.Sheets(1).Cells.Copy activeWB.Sheets(textVAR).Cells
        openWB.Close False
        WP.Show
        Sheets("Program Start").Select
        
    End If
End Sub

and i just repeat the if statement for each "button" cell. My only problem is when there is NOT a file named, i want to open a dialogue box to select a file. Will update when i write that.