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

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
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
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The function code needs to be separate from the Sub code.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,535
Members
417,151
Latest member
ChickenTenderer

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