Add sheet and name it. Please help. VBA to add 107 sheets

[pedie]

Hi,
I want my activeworkbook to add 107 sheets and name sheets as per value from V2 to V108. Is that posible? I current have only one sheet
I want sheet2.name = range v2.value for example and go one till all 107 more sheets are added and name.
Thanks for helping.
Pedie

 

[pedie]

Vog, thank you sooo much. Perfect!

 

[pedie]

Vog, someday back you gave me this code which was very helpfull.
I was trying to make this code run in all the sheets except on "Home" one sheet after another....This is the last question for today I guess

I dont know how I am going thank you for all the help....
Vog, you really do deserve biggest thanks available in this world...

Sub try1()
x = 1
y = 14
c = 13
For chk = x To y
       If Cells(chk, c).Value = "Q" Then
          Cells(chk, c).Value = "R"
          
          Cells(chk, c).Offset(0, -12).Resize(, 12).Copy Destination:=Sheets("Home").Range("B" & Rows.Count).End(xlUp).Offset(1)
        End If
 
Next chk
End Sub

 

[VoG]

Try this

Sub try1()
Const x As Integer = 1
Const y As Integer = 14
Const c As Integer = 13
Dim chk As Integer, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Home" Then
        With ws
            For chk = x To y
                If .Cells(chk, c).Value = "Q" Then
                    .Cells(chk, c).Value = "R"
                    .Cells(chk, c).Offset(0, -12).Resize(, 12).Copy Destination:=Sheets("Home").Range("B" & Rows.Count).End(xlUp).Offset(1)
                End If
            Next chk
        End With
    End If
Next ws
End Sub

 

[pedie]

Perfect!

Vog, thank you for all the help. God bless you and YOU have a Great Weekend!!!

Thanks a million!

 

[pedie]

Pedie, please don't tell us the ONLY difference in the sheets is ONE item (Cell), such as Client Name, Date, or some number reference!!
lenze

Lenze, i'm sure you have a better idea (honestly each sheet is for each single client around 200 of it now...).. updating info from thier location in net.drive so to create confusion to my code i have made each client have 1 sheet each for themself. From where other code is used to consolidate all info together for analysis purpose in another single sheet...i know it is alot of sheets but i could not think of anything better at that moment...
earlier problem arises when 2or more user updates all together and then error happens in page as code find the last empty row in single sheet and updates when both user updates together it gives error or override the prev info..



But i have all the sheets hidden as i dont want other to know i have like 1000000 sheets in one book


Anyways thanks for sharing!!!

 

[VoG]

But i have all the sheets hidden as i dont want other to know i have like 1000000 sheets in one book

You should be aware that if you send this sheet out to your customers it is very easy for them to unhide sheets even if the workbook is protected.

It would be better to have a single sheet where one column is the customer name. You can then filter or run pivots based on that name as required. Furthermore, it is relatively easy to export the data for individual customers to their own sheet or workbook - see http://www.mrexcel.com/forum/showthread.php?t=396069

 

[pedie]

Okay, this file is not for sending out but when a client updates the info they copy the info from thier book to the main book (mt last thread where you helped me with the code modification..thats the same code)and when it paste the info in the main book it used from eg: A1 to L1 etc... so making each column for each client is not possible...
i also once thought i would assign an area in one sheet for each client so that I can use case code that each client update info only in thier area....but since there are lots of user that will be updating info now and then sometime together..i thought the each sheet for each user would make things easier...

 

[VoG]

If it works for you then fine. I am not trying to impose my preferences upon you

 

[pedie]

Yes..for now it is working ..i did design this way and have given in ma boss... i hope it doesnt create prob
I was also surfing around in google and found out in few sites that sharing a workbook especially is prone to file corruption... i hope this does not happen with this spreadsheet
Anyways Peter, thanks again for suggesting... I just thought i better pose another question here again as I was thinking I should take precuations incase problem araises latter u know?

Thanks again