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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,195
Members
430,196
Latest member
rez5656

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
Top