loop to add worksheets then copy and paste values in list

Batemans

New Member
Joined
Sep 15, 2014
Messages
2
I have a workbook with names in column A of sheet1 -
LIST
Bob
Bill
Bill
Barry
Barry
John

I would like to create a new worksheet for each new name in the list;

Sub createwookbook()
Dim i As Integer
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Frow = Cells(Rows.Count, 1).End(xlUp).Row
nextrow = 1
For i = 2 To Frow
If ws.Cells(i, 1) = ws.Cells(i, 1).Offset(-1, 0) Then

' do nothing

Else
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ws.Cells(i, 1).Value
End If
Next i

' This creates the worksheets after sheet 1
' would then like to copy the name in the list to the corresponding worksheet name


For k = 2 To Frow
Select Case ws.Cells(k, 1).Value
Case "Bob"
ws.Cells(k, 1).Copy Destination:=Worksheets("Bob").Cells(nextrow, 1)
nextrow = nextrow + 1
Case "Bill"
ws.Cells(k, 1).Copy Destination:=Worksheets("Bill").Cells(nextrow, 1)
nextrow = nextrow + 1
End Select ' add select for each worksheet name
Next k
nextrow = 1
End Sub

This is close but doesn't quite work (nextrow needs to reset and start at A1 for each new worksheet. Your suggestions would be appreciated. Thanks.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,215,063
Messages
6,122,927
Members
449,094
Latest member
teemeren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top