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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Forum statistics

Threads
1,219,161
Messages
6,146,657
Members
450,706
Latest member
LGVBPP

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