IMPORT WORKSHEET with Prompts for Workbook and Worksheet Name

hartsie

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

hartsie

Board Regular
Joined
May 6, 2020
Messages
74
Office Version
  1. 2016
Would you guys recommend using a VBA automator of sorts for starting out to assist with learning?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

hartsie

Board Regular
Joined
May 6, 2020
Messages
74
Office Version
  1. 2016
I will try that, certainly. I think my knowledge is limited to nested functions.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,056
Its still a start Hartsie. Go for it!
Start with the functions that you are familiar with and you'll be surprised how much you'll learn thereafter.

Cheerio,
vcoolio.
 

hartsie

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

ADVERTISEMENT

Well, one way to get practice would be to answer questions on this site.

So, I’m trying to answer questions on this site. I’m ticked at myself for not trying VBA years ago. I thought I was good with excel until I joined. I’ll keep at it. But this is some tough stuff. I got all kinds of certs. This is a new ball game.
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
74
Office Version
  1. 2016
@mikerickson Can you think of a reason why I am receiving a RUNTIME 13 error when I try to import tabs from some excel files, but it works fine on other excel files? What do you think would be throwing this error? The error occurs right after it opens the selected files and before it provides the option to select a sheet.

It occurs here:
uiSheetName = Application.InputBox("Open which sheet" & vbCr & strPrompt, Type:=2)

'Type Mismatch' error

It has been working great so far. I just can't understand why it is giving me this error on some files. For example, it will successfully work on an older version of 'FILE A.' However, it won't work on a more recent version of 'FILE A'.
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
74
Office Version
  1. 2016
How is uiSheetName dimensioned?
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

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