Creating a worksheet named from a variable cell, and then referencing and accessing that same sheet in the same VBA code program

LesAlk

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Hi Guys
I am attempting to create a new sheet named after a variable cell using Macro VBA code onto which I can paste select data extracted from another sheet. So far, I can create the sheet with the name from the variable cell (Cell with named range "StateSheetName"), but then cannot access that sheet again. Below are the various codes I have tried:

Sheets.Add After:=Worksheets("Income Cat.")
ActiveSheet.Name = Worksheets("Transaction Record").Range("StateSheetName")
Dim sht As Worksheet
Sheets("Income Cat.").Select
Sheets(Worksheets("Transaction Record").Range("StateSheetName")).Select

or

ThisWorkbook.Sheets(Worksheets("Transaction Record").Range("StateSheetName").Value).Activate

Plus a few others. All fail.

Obviously I am no Guru. I would appreciate some help.

Thankyou
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,564
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

or

ThisWorkbook.Sheets(Worksheets("Transaction Record").Range("StateSheetName").Value).Activate
That works for me.

In what way does it fail? Particular error message?
 

LesAlk

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the MrExcel board!


That works for me.

In what way does it fail? Particular error message?
Thanks fo the reply. It fails because whilst I can create the page named from the variable, I cant for the life of me access the created page again using the same variable in he one program. The error message is either "Name already taken" or "Out of range"...
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,564
Office Version
  1. 365
Platform
  1. Windows
In future, when quoting errors, please give the full error message as there sometimes can be slight variations.


"Name already taken"
That error would occur when you tried to rename the new worksheet, not when you were trying to access it. When that error occurs you must be trying to name a worksheet the same as an already existing worksheet. If you click Debug for that error I would expect this line to be highlighted
VBA Code:
ActiveSheet.Name = Worksheets("Transaction Record").Range("StateSheetName")


"Out of range"
This is most likely where your code is trying to refer to a worksheet name that does not exist. When you click Debug for that error, which line of code is highlighted?


Type Mis Match error
Which line of code does this occur on and what is the full error message?
 

LesAlk

New Member
Joined
Mar 28, 2021
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
In future, when quoting errors, please give the full error message as there sometimes can be slight variations.



That error would occur when you tried to rename the new worksheet, not when you were trying to access it. When that error occurs you must be trying to name a worksheet the same as an already existing worksheet. If you click Debug for that error I would expect this line to be highlighted
VBA Code:
ActiveSheet.Name = Worksheets("Transaction Record").Range("StateSheetName")



This is most likely where your code is trying to refer to a worksheet name that does not exist. When you click Debug for that error, which line of code is highlighted?



Which line of code does this occur on and what is the full error message?
My apologies Peter for not quoting the full error message.

You are correct re the "Name already Taken" Error. I have found out for myself after posting that the mistake is occurring because I wasn't deleting the created sheet before running the macro again, hence the name already existing.

Let's see if I can do a better job.

My intention is to create a macro that extracts all data entries between two dates from a selected range of cells from one sheet, "Transaction Record", and paste them into another sheet which has a variable name and date range which I can control through a macro linked to an input table range, creating a monthly statement for each account as required.

The sheet "Transaction Record" is a single sheet, with all formulas for automatic account and position update already entered prior to data entry. All transactions from all accounts go into this one sheet, and are sorted by formulas according to certain codes typed into particular columns. It is the main engine of the whole show.

In order to create a single account statement, I first created another sheet called "Blank Monthly Accounts Sheet", which is almost an exact copy of the transaction sheet, formulas included, but scaled down slightly so as to only reflect one account.

I then created a table on the "Transaction Record" sheet which looks like this:

ScreenShot_2021-03-29_19-39-22.png


Each of the entry boxes in white or off white is a named range which I was intending to use in the macro, apart from the sheet name. I have discovered macros will not work with the cell in which the sheet formula =CONCATENATE(InvNumber," ",F23) is entered, so the named range "StateSheetName" is actually the cell in the bottom left corner, which is a copy. (I should say, I cant get print macros to work with that formula. Not that they don't work...)

And, just for the record, opening balance I type in from the statement, to see if all is correct.

So now I begin the code, and I started with this:

Sheets.Add After:=Worksheets("Income Cat.")
ActiveSheet.Name = Worksheets("Transaction Record").Range("StateSheetName")

That worked. So then I added this:

Sheets("Blank Monthly Accounts Sheet").Select

Cells.Select

Selection.Copy

That Worked.
So then I added this:

Sheets(Worksheets("Transaction Record").Range("StateSheetName")).Select

Which produced the error "Run Time Error 13, Type MisMatch"

Then I tried this:

ThisWorkbook.Sheets(Worksheets("Transaction Record ").Range("StateSheetName ").Value).Activate

Which gives the error " Run time error 9. Subscript out of range"

I tried a few other things, but no show. So at the moment, the way I am trying to do this seems to be no good, as I cant access the sheet I created using the same range from which it n was named.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,170
Messages
5,640,577
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