IMPORT WORKSHEET with Prompts for Workbook and Worksheet Name

hartsie

Board Regular
Joined
May 6, 2020
Messages
84
Office Version
  1. 2016
Hello,

I am trying to improve my process by creating a file that consolidates data sheets.

Because my source file names are variable and always changing, as well as the sheets I want to grab, I was hoping to create a VBA script that first prompts me to open the workbook containing the worksheet I want to copy, then prompts me to name the sheet I want to copy. The workflow would look, at a high level, like this: Clicks Button to run>Prompts me to Find the File>Opens File>Prompts me to input the name of the sheet I want to copy>Copies sheet to destination workbook>Closes source workbook.

I have the script for opening the workbook, I just don't know how to chain the rest. I would appreciate any help, as I am a very new VBA user.

Chris
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hello Hartsie,

Try the following code:-
VBA Code:
Sub WBDataTransfer()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

            Dim stgF As String, stgP As String, shSearch As String
            Dim wb As Workbook
            Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1") '---->Change sheet name to suit.
     
            stgP = "C:\Users\[B]YOUR FILE PATH HERE[/B]"
            stgF = Dir(stgP & "\*.xlsx")
     
            stgF = InputBox("Please enter the name of the required file.")
            If stgF = vbNullString Then Exit Sub
         
            shSearch = InputBox("Please select the required worksheet.")
            If shSearch = vbNullString Then Exit Sub
         
            Set wb = Workbooks.Open(stgP & "\" & stgF)
            stgF = Dir()
     
            wb.Sheets(shSearch).UsedRange.Offset(1).Copy ws.Range("A" & Rows.Count).End(3)(2)
                  
            wb.Close Save = False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

You'll receive two Input Box prompts to enter a file name and then a sheet name. The selected file will then be opened and the entire data set of the selected worksheet will be copied to the destination worksheet ("Sheet1"). The selected file will then be closed.

The code assumes that all files, including the destination file, are in the same folder.

I hope that this helps,

Cheerio,
vcoolio.
 
Upvote 0
You could use this.
VBA Code:
Sub test()
    Dim uiWorkbookPath As String
    Dim uiSheetName As String
    Dim oneSheet As Worksheet, strPrompt As String
    Dim myWorkbook As Workbook, myWorksheet As Worksheet

    uiWorkbookPath = Application.GetOpenFilename
    If uiWorkbookPath = "False" Then Exit Sub: Rem user canceled
   
    Set myWorkbook = Workbooks.Open(uiWorkbookPath)
   
    For Each oneSheet In myWorkbook.Sheets
        strPrompt = strPrompt & ", " & oneSheet.Name
    Next oneSheet
   strPrompt = Mid(strPrompt, 3)
  
    Do
        uiSheetName = Application.InputBox("Open which sheet" & vbCr & strPrompt, Type:=2)
        If uiSheetName = "False" Then
            Rem cancel pressed
            myWorkbook.Close
            Exit Sub
        End If
        If Not (LCase(", " & strPrompt & ", ") Like LCase("*, " & uiSheetName & ", *")) Then
            MsgBox "Bad sheet name" & vbCr & "Try again"
        End If
    Loop Until LCase(", " & strPrompt & ", ") Like LCase("*, " & uiSheetName & ", *")
   
    Set myWorksheet = myWorkbook.Sheets(uiSheetName)

    '   your further code
End Sub
 
Upvote 0
That is brilliant! Thank you!

What is your best tip for learning VBA? Is it really just 'doing' or have you found helpful materials?
 
Upvote 0
That is brilliant! Thank you!

What is your best tip for learning VBA? Is it really just 'doing' or have you found helpful materials?

That is wonderful. I can't believe that you quickly came up with that! How are you so good?
 
Upvote 0
I'm glad it worked for you.
Practice and trying new things is the way I learned. There's no magic book. :)
 
Upvote 0
Well... I am very impressed with both of you. You have been very helpful in a big way.
Thank you, again.
 
Upvote 0
You're welcome Hartsie. I'm glad that we were able to help and thanks for the feed-back.

Ditto what Mike said. Practice and trying, reading/studying various methods, making mistakes then trying again. A Mentor (....and I really wasn't a very good student ;) ) of mine basically said to me, as Mike said, that there isn't a magic book. One can learn the basics but then one needs to develop their own "style".

Cheerio,
vcoolio.
 
Upvote 0
You're welcome Hartsie. I'm glad that we were able to help and thanks for the feed-back.

Ditto what Mike said. Practice and trying, reading/studying various methods, making mistakes then trying again. A Mentor (....and I really wasn't a very good student ;) ) of mine basically said to me, as Mike said, that there isn't a magic book. One can learn the basics but then one needs to develop their own "style".

Cheerio,
vcoolio.

both your methods have taught me some really good short cuts. I actually plan to use both codes for different purposes on the file I’m creating.
Thanks again
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top