cell references
 

i have 2 worksheets

worksheet A
worksheet B

in worksheet A i have a complete row of items inserted (horizontally).
I want to place the information from the row in worksheet A into
Worksheet B (vertically).

challenge:
the row in worksheet A is identified by some text entered into cell B13
and C13. That same text appears as the name of worksheet B. I have
several sheets however I want the identifing text in cell b13 and c13
(or whatever the field it maybe) to add the row info to the worksheet
that bears that same name.

this is the code that currently creates worksheet B

Code:
--------------------

Dim newSheetName As String
Set ws = Worksheets("CGS")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.LstNm.Value) = "" Then
Me.LstNm.SetFocus
MsgBox "Please enter last name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.LstNm.Value
ws.Cells(iRow, 2).Value = Me.FrstNm.Value
newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 2)

'clear the data
Me.LstNm.Value = ""
Me.FrstNm.Value = ""
Me.LstNm.SetFocus

For Each ws In Worksheets
If ws.Name = newSheetName Or _
newSheetName = "" Or _
IsNumeric(newSheetName) Then
MsgBox "Sheet already exists or name is invalid", vbInformation
Exit Sub
End If

Next
Sheets("SS").Copy befo=Sheets(1)
Sheets(1).Name = newSheetName
Sheets(newSheetName).Move After:=Sheets(Sheets.Count)

'close userform
Unload Me
End Sub

--------------------


Thanx for checking this out and helping


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=466571