Solved: With-End With problem

[YellowLabPro]

I changed this
[VBA]
wsTarget.Range("A4:T" & lrTarget).Delete
wsTarget.Range("A4:T" & lrTarget).Value = wsSource.Range("A4:T" & lrSource).Value
wsTarget.Range("A4:T" & lrTarget)..Font.Bold = False
[/VBA]

To this
[VBA]
With wsTarget.Range("A4:T" & lrTarget)
.Delete
.Value = wsSource.Range("A4:T" & lrSource).Value
.Font.Bold = False
End With
[/VBA]

But this line
[VBA]
.Value = wsSource.Range("A4:T" & lrSource).Value
[/VBA]

is giving me an error-
run time 424
Object required

Any ideas why this is not a valid block?

[Norie]

Doug

You're deleting the range object immediately before you try and use it's value property.

[YellowLabPro]

Norie,
I dont think so. But I say this cautiously....
Let me repost.
I am deleting the Target, then telling it to use the Source sheet.

This works
[VBA]With wsTarget
.Range("A4:T" & lrTarget).Delete
.Range("A4:T" & lrSource).Value = wsSource.Range("A4:T" & lrSource).Value
.Range("A4:T" & lrSource).Font.Bold = False
End With[/VBA]

This does not work
[VBA]
With wsTarget.Range("A4:T" & lrTarget)
.Delete
.Value = wsSource.Range("A4:T" & lrSource).Value
.Font.Bold = False
End With
[/VBA]

[Norie]

No you are deleting the range object.

That might not be technically the right phrase but I suggest you try stepping throughh this code and add a watch to rng.
[vba]
Set rng = wsTarget.Range("A4:T" & lrTarget)
With rng
.Delete
.Value = wsSource.Range("A4:T" & lrSource).Value
.Font.Bold = False
End With[/vba]

[YellowLabPro]

I will try that, thanks Norie

[Bob Phillips]

When you delete a cell, if you are pointing at that cell your reference is lost.

This codes resets the pointer to the new cell that now occupies the position of the deleted cell

[vba]

With wsTarget
.Range("A4:T" & lrTarget).Delete
.Range("A4:T" & lrSource).Value = wsSource.Range("A4:T" & lrSource).Value
.Range("A4:T" & lrSource).Font.Bold = False
End With
[/vba]

whereas this code has not been reset (because the cell is included in the With), so itsi trying to pibt at the cell the was deleted, which is invalid

[vba]

With wsTarget .Range("A4:T" & lrTarget).Delete
.Range("A4:T" & lrSource).Value = wsSource.Range("A4:T" & lrSource).Value
.Range("A4:T" & lrSource).Font.Bold = False
End With
[/vba]

[YellowLabPro]

Bob,
Possible you pasted the same line of code in the two examples? I see no difference between the two lines of code.

[Bob Phillips]

It got screwed up, look at it now I have corrected it.

[YellowLabPro]

ok.... got it

gracias

[mdmackillop]

Instead of Delete, use ClearContents