VBA question relatated to another answer given

this question is related to the answer provided here http://www.experts-exchange.com/questions/28710206/copy-data-to-new-sheets-each-time-name-changes.html?anchor=a40956348

i am a excel learner, so i have two question on this code.

1- i added some new data with different in column B and Column A and then ran the macro then i get an error in line 11 and it says "Run time error 1004
Cannot Rename a sheet to the same name as another sheet a referenced object library or a workbook referenced by Visual Basic."

2- i got confused with "lastvalue"  you have not set any value or i do not see any reference before for "lastvalue" can you please explain why have you used If c.Value <> lastvalue Then
        lastvalue = c.Value    becuase "lastvalue"  obiously has no value and c.value is definitely is not equal to c.value , so wouldn't this work instead  lastvalue=c.Value


grateful if you could shed light on this.
LVL 3
excelismagicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

1. you cannot name a sheet a name already used

2. the use of last value is to create a sheet only for one of each distinct value, lastvalue (before assigning c.value) is Null which is also <> c.value

Regards
excelismagicAuthor Commented:
Rgonzo1971

thanks  2 is clarified.

but 1. still gives error and the sheet name is not used before.

please see attached.  i added new data value 981 and there is no sheet with this value name.

then why it gives error.

thanks for looking into this.
Sheets.xlsm
Rgonzo1971Commented:
The macro begins from the line 2 and 123 already exists
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this......
It's always best practice to declare variables used in the code. Also if you run the previous code second time, you will get an error because all the sheets already exist in the workbook which were created when the code was run first time. And when you try to run it next time, code cannot create two sheets with similar names hence produces an error. So better delete those sheets before running the code again. I have added a code to delete the sheets if they already exist in the workbook.


Sub macro1()
Dim OriWs As Worksheet, newWs As Worksheet
Dim c As Range
Dim sh As String
Dim lastvalue

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set OriWs = Sheets("Consolidated")
OriWs.Activate
OriWs.UsedRange.Sort key1:=ActiveSheet.Range("B1"), Header:=xlYes

For Each c In Range(Range("B2"), Range("B" & Cells.Rows.Count).End(xlUp))
    If c.Value <> lastvalue Then
        lastvalue = c.Value
        sh = c.Value
        On Error Resume Next
        Sheets(sh).Delete
        On Error GoTo 0
        Set newWs = ActiveWorkbook.Sheets.Add(after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count))
        newWs.Name = c.Value
        OriWs.UsedRange.AutoFilter Field:=2, Criteria1:=c.Value
        OriWs.Cells.Copy newWs.Range("a1")
    End If
Next
OriWs.UsedRange.AutoFilter
Application.DisplayAlerts = True
Application.DisplayAlerts = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
excelismagicAuthor Commented:
Many thanks to both of you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.