FU: Distribute info from "summary" page to separate pages under condition

M

markx

Hi guys,

this is a follow-up to the post I've submitted one hour ago...
-----
In the meantime, I've found somewhere on the newsgroups an interesting post
from 2003, submitted by Brian Wilson, with the code doing "sorting data into
different pages" or, more specifically, "copying rows to worksheets based on
value in column A" (I enclose the code below). It works quite fine, with
only one exception:

Set Targetsht = ActiveWorkbook.Worksheets(Curr­entCellValue)
'note: using CurrentCell.value gave me an error if the value was
numeric

Does any of you have an idea how to modify the code in order to include also
numeric values as possible variables?
Thanks in advance for sharing your ideas...

Cheers,
Mark

-----
Here's the code:

Sub CopyRowsToSheets()
'copy rows to worksheets based on value in column A
'assume the worksheet name to paste to is the value in Col A
Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String


'start with cell A2 on Sheet1
Set CurrentCell = Worksheets("sheet1").Cells(2, 1) 'row 2 column 1


Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
Set SourceRow = CurrentCell.EntireRow


'Check if worksheet exists
On Error Resume Next
Testwksht = Worksheets(CurrentCellValue).N­ame
If Err.Number = 0 Then
'MsgBox CurrentCellValue & " worksheet Exists"
Else
MsgBox "Adding a new worksheet for " & CurrentCellValue
Worksheets.Add.Name = CurrentCellValue
End If


On Error GoTo 0 'reset on error to trap errors again


Set Targetsht = ActiveWorkbook.Worksheets(Curr­entCellValue)
'note: using CurrentCell.value gave me an error if the value was
numeric


' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1
SourceRow.Copy Destination:=Targetsht.Cells(T­argetRow, 1)


'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)
Loop
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top