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:
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.
