IMPORT WORKSHEET with Prompts for Workbook and Worksheet Name

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,056
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,970
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
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
That is brilliant! Thank you!

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

hartsie

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

ADVERTISEMENT

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?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,970

ADVERTISEMENT

I'm glad it worked for you.
Practice and trying new things is the way I learned. There's no magic book. :)
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
Well... I am very impressed with both of you. You have been very helpful in a big way.
Thank you, again.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,056
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.
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
77
Office Version
  1. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,846
Members
416,138
Latest member
Pizzaman22

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
Top