Passing a cell/worksheet/workbook in a macro

[vbace2]

Hope this makes sense...

I am trying to write a macro that will take data from a cell in one spreadsheet, create a new workbook, and then populate some fields in the new workbook from other data in the same row as the original cell.

I first started off with creating a macro that will do this for a single row. However, I may need to run this for multiple rows in the original spreadsheet. So, I was trying to create a driver macro that would pass the selected cell to the other macro. I'm having trouble with this. I'm not sure what I should be passing (i.e. worksheet, cell, ect.).

Here are some things that I am trying to do and how I am doing them in the first macro that I created...

Dim FromWS As Worksheet
Dim ToWS As Worksheet
Dim NewWSName As String
Dim FromWBName As String
Dim SRNumber As String

If ActiveSheet.Name <> "ActiveSRs" Then
MsgBox "Wrong sheet selected.", _
vbExclamation
Exit Sub
End If

If ActiveCell.Column <> 1 Then
MsgBox "Should be in column 1.", _
vbExclamation
Exit Sub
End If

SRNumber = ActiveCell.Value
If Len(SRNumber) <> 7 Or _
Left(SRNumber, 2) <> "SR" Or _
(Not IsNumeric(Right(SRNumber, 5))) Then
MsgBox "Invliad SR Number selected.", _
vbExclamation
Exit Sub
End If

I tried to create the second macro where I am receiving a worksheet called FromWS. I am passing ActiveWorkbook.Worksheets("ActiveSRs"). My first IF still works, but I can't get to the cell reference. I get a type mismatch. Please see below.

Dim ToWS As Worksheet
Dim NewWSName As String
Dim FromWBName As String
Dim SRNumber As String

If FromWS.Name <> "ActiveSRs" Then
MsgBox "Should be in column 1.", _
vbExclamation
Exit Sub
End If

If FromWS.Cells <> 1 Then
MsgBox "Should be in column 1.", _
vbExclamation
Exit Sub
End If

Any thoughts? Am I trying to pass the wrong thing? If I can get some direction, I am sure I can figure the other issues I am having.

[duane]

I'm not that great at the vba coding myself, but (assuming you are looking for the column of the active cell in your first worksheet)

youminght try this

If FromWS.activeCell.column <> 1 Then

[duane]

I played around with it a little and this seems better - but again I am not that good with vba

sheets("fromws").activate
if Sheets("fromws").Cells(1, ActiveCell.Column).Column <> 1 Then


you will later need to activate the other worksheet