Copying Sales sheet and renaming the sheets according to the name coloumn in the Basic Sheet

[Islam Mamdouh Ali]

Dear All,

I need your urgent help in the following case:

Here I have a sales sheet and I rename it according to each sales man. I need a VBA code to copy the sales sheet and renaming it according to the sales men name listed in the names column in the basic sheet as attached.

so, I need to copy the sales sheet according to the number of salesmen listed in "Name column" in the basic sheet and renaming each sheet according to this names.

your feedback is highly appreciated.

thanks in advance.

https://ufile.io/uk5s1

 

[My Aswer Is This]

And what column is the "Name column" Why not say column "B" or column "E"

And you want to add the new sheet to the current workbook?

You said copy the sheet but did not say what to do with the copy.

And what is the name of the sales sheet.

You should say sheet named "Sales" if that is correct.
And what is the name of the basic sheet

You said:
renaming it according to the sales men name listed in the names column in the basic sheet

I never open links.

 

[Islam Mamdouh Ali]

Thank you for your support.

I have presented my issue in this way as I am VBA beginner and I attached the workbook link in order to make it easy to any one to help me.

according to your above inquires you will find here my replies:

- "Name Column" is column(A)
- Yes, I want to copy the new sheets in the same workbook.
- I only need to copy this sheets according to the number of sales men listed in sheet 1 ("Basic sheet") In column A.
SO, for example if I have listed 100 salesmen in column A in sheet 1 ("Basic sheet")the code will copy Sheet 2 ("Sales sheet") 100 time and rename each sheet according to each sales man name listed in column A.

That is all.

I hope to get your support in view of the above mentioned information.

Kind regards,

 

[My Aswer Is This]

Try this:

Be sure your sheet names are exactly correct. I set them the way you said.
If not correct the script where colored "Red"
This script starts looking for names in row (2) Column ("A") of sheet named "Basic"
If you want it to start looking for names in row(1) change 2 to 1 where marked in red.

Sub Copy_Sheets()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim ans As String
Dim anss As String
ans = "[COLOR=#ff0000]Basic sheet[/COLOR]" 'Be sure this name is correct
anss = "[COLOR=#ff0000]Sales sheet[/COLOR]" 'Be sure this name is correct
Sheets(ans).Activate
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row
    For i = [COLOR=#ff0000]2[/COLOR] To Lastrow
        Sheets(anss).Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Sheets(ans).Cells(i, 1).Value
    Next
Application.ScreenUpdating = True
End Sub

 

[Islam Mamdouh Ali]

Thank you for your efforts,it worked perfect.

I didn't find words to thank you for this work.

in the meantime, I need your urgent advise I need to learn VBA. Please tell me how to begin?

- watching videos, if so please suggest for me any YouTube channels.
- Books, please suggest for me books to read.

I need to study from A to Z.

your feedback is highly appreciated.

Regards,

 

[My Aswer Is This]

Glad I was able to help you:
Take a look here and see if you can find some help learning vba Here: http://www.mrexcel.com/learnexcel.shtml


Also I always start trying to learn the code in scripts I see here on this forum and test some out for myself and start out doing small projects

For example:

Cells(1,1).value="George" This will put the word "George" in Range("A1")
Range("A1") and Cells(1,1) both mean the same thing

Or if you want "George" in a large range do this:

Range("A1:A20").value="George"

These small examples maybe can get you going.
Or you may already know more then this.

Again I always start out small and work my way up.
But then others I see on this forum say they are new to Vba but want code written for them that will do all types of things. Well that's great but I wonder do they understand any of the code and try to learn from it or do they come here just to get a answer which is fine also.

Some coming here may be very fast learners and want to learn to drive sports cars before they learn to ride tricycles.

Give me another challenge you may have and lets see if I can help you with another project,

Take care.
Now the script I wrote is the best I can do but then there are always others who may be able to write you another script which may do the same thing but do it 3 seconds faster. And that's fine also.

Thank you for your efforts,it worked perfect.

I didn't find words to thank you for this work.

in the meantime, I need your urgent advise I need to learn VBA. Please tell me how to begin?

- watching videos, if so please suggest for me any YouTube channels.
- Books, please suggest for me books to read.

I need to study from A to Z.

your feedback is highly appreciated.

Regards,

 

[Islam Mamdouh Ali]

Thank you for your polite reply.