Message Box Input to Dictate What Sheet to Import

[jski21]

Good day most excellent Excellers,

Looking to import a specific sheet from a workbook that will always have the same 12 sheets (month names). Trying to create a message box that will ask for the month, use that as a variable for the sheet to import, and ultimately bring it into the workbook. This is a new frontier for me so I'm asking the super users here for a bit of assistance. Here's the code I've got so far (which doesn't work btw):


'Declared Variables
Dim PrgmInc As String
Dim SourceWB As Workbook


'Open Input Box and Ask User for month.
PrgmInc = Application.InputBox("Please provide the month for program income:", "PrgmInc", Type:=1)


'--------------------------------------------------------------------------------------------------------

'Sets the variables so as to bring in specified month tab from another workbook
Application.ScreenUpdating = False
Set SourceWB = Workbooks.Open("\\cd-srv2\acctgshare$\Program Income Reports\Receiving Warrants\Receiving Warrants 2022.xlsx\TAB NAME?")
SourceWB.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
SourceWB.Close
Application.ScreenUpdating = True


'Turn On Screen Updating and Application Alerts
Application.ScreenUpdating = True
Application.DisplayAlerts = True


Thanks in advance to all for the consideration and help.


jski

 

[swapnilk]

Try this code:

Sub ImportSheet()
    
    'Declare variables to hold the month name, the file path, and the sheet to import
    Dim monthName As String
    Dim filePath As String
    Dim sheetToImport As Worksheet
    
    'Set the file path to the location of the workbook
    filePath = "\\cd-srv2\acctgshare$\Program Income Reports\Receiving Warrants\Receiving Warrants 2022.xlsx"
    
    'Prompt the user to enter the month name with a custom message
    monthName = InputBox("Please provide the month for program income:", "Import Sheet")
    
    'Open the workbook and set the sheet to import
    Set sheetToImport = Workbooks.Open(filePath).Sheets(monthName)
    
    'Import the sheet into the current workbook
    sheetToImport.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    'Close the workbook and clean up by releasing the reference to the sheet
    Workbooks(filePath).Close
    Set sheetToImport = Nothing
    
End Sub

 

[jski21]

This is good swapnilk. Thanks. Almost there. I keep my PERSONAL workbook open and it seems to be importing the sheet into that file rather than the target file titled Program Income.xlsx even though I had it open and activated.

 

[jski21]

Also getting a Subscript out of range error by:

'Close the workbook and clean up by releasing the reference to the sheet
Workbooks(filePath).Close

 

[swapnilk]

This is good swapnilk. Thanks. Almost there. I keep my PERSONAL workbook open and it seems to be importing the sheet into that file rather than the target file titled Program Income.xlsx even though I had it open and activated.

You need to run the below code inside your target file i.e. Program Income.xlsx.

Sub ImportSheet()
   
    'Declare variables to hold the month name, the file path, and the sheet to import
    Dim monthName As String
    Dim filePath As String
    Dim sheetToImport As Worksheet
   
    'Set the file path to the location of the workbook
    filePath = "C:\Users\Username\Desktop\Book2.xlsx"
   
    'Prompt the user to enter the month name with a custom message
    monthName = InputBox("Please provide the month for program income:", "Import Sheet")
   
    'Open the workbook and set the sheet to import
    Set sheetToImport = Workbooks.Open(filePath).Sheets(monthName)
   
    'Import the sheet into the current workbook
    sheetToImport.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
   
End Sub

 

[jski21]

Yes, this does work. Thanks. The target file will change every month. It will have the same name, just different data based on the timeframe of the download so I would need to write this code in each new file, yes? Is there some modification(s) to have it run from the Personal macro workbook?

 

[swapnilk]

Try below code in your Personal workbook:

Sub ImportSheet()
    
    'Declare variables to hold the month name, the file paths, and the sheets to import
    Dim monthName As String
    Dim sourceFilePath As String
    Dim targetFilePath As String
    Dim sheetToImport As Worksheet
    
    'Set the file paths
    sourceFilePath = "\\cd-srv2\acctgshare$\Program Income Reports\Receiving Warrants\Receiving Warrants 2022.xlsx"
    targetFilePath = "\\cd-srv2\acctgshare$\Program Income Reports\Receiving Warrants\Program Income.xlsx"
    
    'Prompt the user to enter the month name with a custom message
    monthName = InputBox("Please provide the month for program income:", "Import Sheet")
    
    'Open the source workbook and set the sheet to import
    Set sheetToImport = Workbooks.Open(sourceFilePath).Sheets(monthName)
    
    'Open the target workbook
    Workbooks.Open targetFilePath
    
    'Import the sheet into the target workbook
    sheetToImport.Copy After:=Workbooks(targetFilePath).Sheets(Workbooks(targetFilePath).Sheets.Count)
    
End Sub

 

[jski21]

Ok up until the last statement:

'Import the sheet into the target workbook
sheetToImport.Copy After:=Workbooks(targetFilePath).Sheets(Workbooks(targetFilePath).Sheets.Count)


Then this appears:

 

[jski21]

Switched a few things around and got this to work error free:

'Set the file paths
sourceFilePath = "SOURCE FILE PATH AND NAME OF SORCE EXCEL FILE GO HERE"
targetFilePath = "TARGET FILE PATH AND NAME OF TARGET FILE GOES HERE"


'Prompt the user to enter the month name with a custom message
monthName = InputBox("Please provide the month for program income:", "Import Sheet")


'Open the source workbook and set the sheet to import
Set sheetToImport = Workbooks.Open(sourceFilePath).Sheets(monthName)


'Copy the current month sheet into the target workbook after Tab 2
Sheets(monthName).Select
Sheets(monthName).Copy Before:=Workbooks("NAME OF TARGET FILE").Sheets(2)


'Close the source workbook
Workbooks("NAME OF SOURCE FILE").Close Savechanges:=False