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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,716
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,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

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