An unfortunate name of worksheets (A1 thru A10). It makes it look like cell
addresses.

But maybe something like this will show you one way to go through the visible
cells:

Option Explicit
Sub testme()

Dim fWks As Worksheet 'from worksheet
Dim iCtr As Long
Dim rngF As Range
Dim myCell As Range

With ActiveSheet.AutoFilter.Range
Set rngF = Nothing
On Error Resume Next
Set rngF = .Columns(1).Cells.Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
'only the header is visible
MsgBox "no details shown"
Else
iCtr = 0
For Each myCell In rngF.Cells
iCtr = iCtr + 1
If WorksheetExists("a" & iCtr, ActiveWorkbook) Then
'it's there
Else
'add it
Worksheets.Add
ActiveSheet.Name = "A" & iCtr
End If

myCell.EntireRow.Copy _
Destination:=Worksheets("a" & iCtr).Range("a1")
Next myCell

End If
End With
End Sub
Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

The WorksheetExists function was taken from a Chip Pearson post. (I like it!)

Dennis wrote:

Unsing 2003

Created a macro to add then copy/past cell info from one worksheet to a
series of other new worksheets. Works fine.

The reason for the macro was to automate the process of adding a worksheet
(which is limited to the 255 character limit) then copy/paste cells so as to
overcome the 255/per cell limitation.

Now I have a new series of worksheets "A 1 thru 10".

I would like to populate the cells of the new worksheets with certain cells
existing on another worksheet, Named "B", which has filtered data.

Thus worksheet A1 has 10 cells (in a different layout on W/S "B"). I want
to populate A1 then, A2, A3 .... etc., with cells from W/S "B" where the
"Visible Rows" may be
Row 3 next 7 next 20, next 57.

So A1 is populated with W/S "B" Row 3 information
A2 is populated with W/S "B" Row 7 info
A3 is populated with W/S "B" Row 20 info (and so on)

I am not sure how to cause a VBA loop to skip through W/S "B" visible rows,
populate the various "A" series W/S and then stop when the last visible row
on "B" is encountered.

So I need a counter? that increments non-sequentially?
knows how many "A" W/S to populate and stops when all visible row
information is completed.

Not sure whether to use .Offset() or what ever.

Any help would be appreciated.

Dennis

BTW the macro so far is:

Sub WorkSheetCopy()
'
'
' Assumes that the ActiveSheet is the Copy-from Worksheet
'
'
'
' Keyboard Shortcut: Ctrl+Shift+W
'
'
Dim WorkSheetNumber As Long
Dim OrigWorkSheetName As String
ActiveSheet.Select
OrigWorkSheetName = ActiveSheet.Name
ActiveSheet.Copy After:=ActiveWorkbook.Worksheets _
(ActiveWorkbook.Sheets.count)
WorkSheetNumber = ActiveWorkbook.Sheets.count
Sheets(OrigWorkSheetName).Select
Cells.Select
Selection.Copy
Sheets(WorkSheetNumber).Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Sheets(OrigWorkSheetName).Select
Range("A1").Select
End Sub

--

Dave Peterson