Macro for copying cells in multiple sheets

[Tumbad]

Hi,

Need help for below condition.

I have excel data in A3 to O500

I need to copy each row (A3 to O3, A4 to O4, A5 to O5 and so on till A500 to O500) in each new worksheet from B2 to P2 (so 500 sheets).

Please assist,
Best,
Tumbad

 

[My Aswer Is This]

So are you saying you want the script to create 500 new sheets. And then copy the data as you mentioned to those sheets.

Or have you already created these 500 sheets.

And if you want the script to create these new sheets how do you want these sheets named?

 

[Tumbad]

So are you saying you want the script to create 500 new sheets. And then copy the data as you mentioned to those sheets.

Or have you already created these 500 sheets.

And if you want the script to create these new sheets how do you want these sheets named?

New sheets to be created and named as cell B2 of that sheet after copy/paste

 

[My Aswer Is This]

Assuming your original sheet is sheet(1)
I have provided a script for you to test.
Run this script from Sheet 1
This script will make 3 new sheets with the data you want.

If this script works then change 3 to 5 to 3 to 500

Lets not start with 500 till we know this is what you want.

If it works then delete the 3 new sheets and run the script again using 3 to 500

Sub Make_New_Sheets()
'Modified  11/19/2019  8:08:04 AM  EST
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
On Error GoTo M
    For i = 3 To 5 ' Modify 5 to 500 here if this works
        ans = Sheets(1).Cells(i, 1).Value
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = ans
            Sheets(1).Cells(i, 1).Resize(, 15).Copy Sheets(ans).Cells(2, 2)
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "We had a error" & vbNewLine & "You may have a duplicate sheet name or a improper sheet name"

End Sub

 

[Tumbad]

Its working for 3 rows, thanks will attempt now for 500 rows.

 

[My Aswer Is This]

So did this work for 500 sheets. I will be signing off soon here.
Not sure how creating 500 new sheets will work I did not test on 500.

If you get a error message check and see if all 500 cells have proper sheet names.

Excel only allows certain values to be used as sheet names.

Would be interested to know how long this script took to run with 500