VBA Import columns by headers - Need prompts to select workbook/sheet name

[floggingmolly]

I have a code that imports data from a workbook based off of the headers. This work, but I have to specify a workbook and sheet in the code. I need for the user to be able to select a workbook and sheet name. Some of the workbooks can have many sheets. What I would like is for the user to be able to select a workbook and have it open, then a way for them to enter a sheet name to activate. The workbook would need to open so they can see what sheet names are available. Below is the code I have so far if anybody can assist:

Sub pull_columns()

Dim head_count As Integer
Dim row_count As Integer
Dim col_count As Integer
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets("Sheet1")

'count headers in this workbook
head_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))

'open other workbook and count rows and columns
Workbooks.Open Filename:="C:\Users\672538\OneDrive - MyFedEx\Desktop\EXCEL COPY COLUMNS BASED ON HEADER\Template.xlsx"
ActiveWorkbook.Sheets(1).Activate

row_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
col_count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlToRight)))


For i = 1 To head_count

    j = 1
    
    Do While j <= col_count
    
        If ws.Cells(1, i) = ActiveSheet.Cells(1, j).Text Then
        
            ActiveSheet.Range(Cells(1, j), Cells(row_count, j)).Copy
            ws.Cells(1, i).PasteSpecial xlPasteValues
            Application.CutCopyMode = False
            j = col_count
        
        End If
    
    j = j + 1
    
    Loop

Next i

ActiveWorkbook.Close savechanges:=False

ws.Cells(1, 1).Select

Application.ScreenUpdating = True

End Sub
[\code]

 

[gplhl]

You could possibly get the worksheet names before they open it. I had recalled someone else needed worksheet names before.

Get worksheet names from closed workbook

Hello, Does anyone have VBA code that will get all worksheet names from a closed workbook. Thank you in advance. Aloha, Rey

www.mrexcel.com

 

[floggingmolly]

You could possibly get the worksheet names before they open it. I had recalled someone else needed worksheet names before.

Get worksheet names from closed workbook

Hello, Does anyone have VBA code that will get all worksheet names from a closed workbook. Thank you in advance. Aloha, Rey

www.mrexcel.com

My issue is they can have several workbooks with different names. I am looking for a way that they can choose the workbook, have it open, then select a sheet name from that workbook.