Import data using code name instead of Sheet name (VBA)

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
644
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I have a file where I import data, but the person keeps on changing the Sheet name / front of house name.

So I'd like to use the code name / back of house name of the Sheet, when importing the data.

Does anyone know how I can use the code name / back of house name instead of the Sheet name in the code below, please?

So I want to change this: Worksheets("Sheet1").Select so that it refers to the name in the VB editor. Sometimes the front of house name is called "Sheet1" and other times it's called "Main" etc.

VBA Code:
Sub GetData()

Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim ReportP As Workbook
Dim iWBP As Workbook

Set ReportP = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Main Folder"
filewaschosen = fd.Show
fd.Execute

Set iWBP = ActiveWorkbook
'Get data from  file
Worksheets("Sheet1").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:AE" & lastrow).Copy
 
You have to move the function outside the Sub and delete Sheet1.Select and you're good to go. The function is another procedure, like your sub.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
The function code needs to be separate from the Sub code.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,784
Members
448,297
Latest member
carmadgar

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