tom - yes, wsCmp is the active sheet. but this is just the first
round of range-setting. then there's another round on wsRpt before
actually transferring stuff. but i see what you mean, since this is a
private worksheet sub, then i shouldn't have to qualify which range
i'm setting if it's already on this worksheet. (can i do it anyway,
just for my own sake of mind? (using the with-end with))

cory - changing the "&" to a "," worked fine (using the with).

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

thank you both!
susan



On May 30, 3:05 pm, Tom Ogilvy
wrote:
That will raise an error if wsCmp isn't the active sheet and if it is, then
you don't need to qualify. If you qualify one, you should qualify all (or
use With as she was already doing).

--
Regards,
Tom Ogilvy



"JLGWhiz" wrote:
Try:

Set r1 = wsCmp.Range(Cells(7, Lx), Cells(12, Lx))
Set r2 = wsCmp.Range(Cells(18, Lx),Cells(23, Lx))

"Susan" wrote:

in transferring data in these 2 sheets, the rows will always stay the
same, but the column will change..........

Private Sub CommandButton1_Click()

Dim wsRpt As Worksheet, wsCmp As Worksheet
Dim wb As Workbook
Dim q1 As Range, q2 As Range, q3 As Range
Dim q4 As Range, q5 As Range, q6 As Range
Dim q7 As Range, q8 As Range, q9 As Range
Dim q10 As Range, q11 As Range, q12 As Range
Dim q13 As Range
Dim r1 As Range, r2 As Range, r3 As Range
Dim r4 As Range, r5 As Range, r6 As Range
Dim r7 As Range, r8 As Range, r9 As Range
Dim r10 As Range, r11 As Range, r12 As Range
Dim r13 As Range
Dim rNumber As Range, rTotalNumber As Range
Dim OriginalNumber As Long
Dim Lx As Long, Cx As Long
Dim UpdatedAs As Date
Dim rUpdate As Range

'set everything up
Set wb = ActiveWorkbook
Set wsCmp = wb.Worksheets("Comparisons")
Set wsRpt = wb.Worksheets("Report")
Set rNumber = wsRpt.Range("w8")

OriginalNumber = rNumber.Value
UpdatedAs = Format(Now, "mm/dd/yy")

'the command .cells always has (row) first and then (column)

Cx = wsCmp.Cells(7, 1).Row
Lx = wsCmp.Cells(7, 200).End(xlToLeft).Offset(0, 1).Column
'MsgBox Cells(Cx, Lx).Address

Set rUpdate = wsCmp.Cells(6, Lx)
Set rTotalNumber = wsCmp.Cells(13, Lx)

'*****EVERYTHING ABOVE HERE WORKS

now comes going down in flames.......... first i tried
Set r1 = wsCmp.Range(Lx & "7:" & Lx & "12")
Set r2 = wsCmp.Range(Lx & "18:" & Lx & "23")

but that didn't work, because Lx is an integer, not a letter. (i
realize i can turn Lx into a string, but i want to see how you make it
work this way.)

after searching the newsgroup, i tried

With wsCmp
Set r1 = .Range(.Cells(7, Lx) & .Cells(12, Lx))
Set r2 = .Range(.Cells(18, Lx) & .Cells(23, Lx))
End With

but that won't work, either.
i also tried
Set r1 = .Range(.Range(.Cells(7,Lx) & .Cells(12,Lx))))
but no dice, there, either.

i'm getting a run-time 1004 error

can somebody please correct whatever syntax error i've got going on?
thank you very much!
susan- Hide quoted text -

- Show quoted text -