with, end with
 

greetings, i'm using with and end with to copy a row, the with statement
specifying a worksheet. i need to do this operation over couple worksheets,
and when i run macro there's a bug. doesn't seem to do instructions in with
statement on sheets that aren't the active window. how can i correct this?
here's code:

Sub database()

Dim oldr As Integer
Dim newr As Integer
Dim width As Integer

oldr = Range("cCollar").Rows.count
newr = Range("grid").Rows.count
width = Range("grid").Columns.count

If newr < oldr Then
Worksheets("rating").Range(Cells(oldr + 3, 2), Cells(newr + 2, width +
1)).ClearContents
Worksheets("output").Range(Cells(oldr + 3, 3), Cells(newr + 2, width +
1)).ClearContents
End If

If oldr < newr Then
With Worksheets("rating")
.Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2),
Cells(newr + 3, 2))
End With

With Worksheets("output")
.Range(Cells(4, 3), Cells(4, width + 1)).Copy .Range(Cells(5, 3),
Cells(newr + 3, 3))
End With
End If

End Sub

with, end with
 

try a dot . in fron of cells

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mwam423" wrote in message
...
greetings, i'm using with and end with to copy a row, the with statement
specifying a worksheet. i need to do this operation over couple
worksheets,
and when i run macro there's a bug. doesn't seem to do instructions in
with
statement on sheets that aren't the active window. how can i correct
this?
here's code:

Sub database()

Dim oldr As Integer
Dim newr As Integer
Dim width As Integer

oldr = Range("cCollar").Rows.count
newr = Range("grid").Rows.count
width = Range("grid").Columns.count

If newr < oldr Then
Worksheets("rating").Range(Cells(oldr + 3, 2), Cells(newr + 2, width +
1)).ClearContents
Worksheets("output").Range(Cells(oldr + 3, 3), Cells(newr + 2, width +
1)).ClearContents
End If

If oldr < newr Then
With Worksheets("rating")
.Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2),
Cells(newr + 3, 2))
End With

With Worksheets("output")
.Range(Cells(4, 3), Cells(4, width + 1)).Copy .Range(Cells(5, 3),
Cells(newr + 3, 3))
End With
End If

End Sub

with, end with
 

Mind your dots... you have this line...

With Worksheets("rating")
.Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2),
Cells(newr + 3, 2))
End With

Where you have cells with no dot it is the same as
Activesheet.cells and not Worksheets("rating").cells

With Worksheets("rating")
.Range(.Cells(4, 2), .Cells(4, width + 1)).Copy .Range(.Cells(5, 2),
..Cells(newr + 3, 2))
End With

Should be better...
--
HTH...

Jim Thomlinson


"mwam423" wrote:

greetings, i'm using with and end with to copy a row, the with statement
specifying a worksheet. i need to do this operation over couple worksheets,
and when i run macro there's a bug. doesn't seem to do instructions in with
statement on sheets that aren't the active window. how can i correct this?
here's code:

Sub database()

Dim oldr As Integer
Dim newr As Integer
Dim width As Integer

oldr = Range("cCollar").Rows.count
newr = Range("grid").Rows.count
width = Range("grid").Columns.count

If newr < oldr Then
Worksheets("rating").Range(Cells(oldr + 3, 2), Cells(newr + 2, width +
1)).ClearContents
Worksheets("output").Range(Cells(oldr + 3, 3), Cells(newr + 2, width +
1)).ClearContents
End If

If oldr < newr Then
With Worksheets("rating")
.Range(Cells(4, 2), Cells(4, width + 1)).Copy .Range(Cells(5, 2),
Cells(newr + 3, 2))
End With

With Worksheets("output")
.Range(Cells(4, 3), Cells(4, width + 1)).Copy .Range(Cells(5, 3),
Cells(newr + 3, 3))
End With
End If

End Sub

with, end with
 

thanks, jim, thanks don, and thank god for this board! have a great weekend
everybody =D


"Jim Thomlinson" wrote:

Mind your dots... you have this line...