writing better code

[SQUIDD]

Hi All

I am trying to re-write my code better, rather than posting my entire code, I am adjusting what I know I can do.
I have just got stuck at this bit.

I was using this

Sheets("GET MEETING FROM RP").Select: Range("A1").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False: Range("A1").Select

But i think it may be better written something like this.

With Worksheets("GET MEETING FROM RP")
.Pictures.Delete
.Columns("A:AA").Clear
IE1.ExecWB 17, 0: IE1.ExecWB 12, 2
Range("A1").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False: Range("A1").Select

I know i have extra code on this one, i just wanted you to see the with worksheets part.

Any one know what i am doing wrong???

Thanks

 

[xenou]

Hi,

With's need End With's.

Also the Range needs to be dotted to "chain" it to the worksheet you are "withing".

[COLOR="Navy"]With[/COLOR] Worksheets("GET MEETING FROM RP")
    .Pictures.Delete
    .Columns("A:AA").Clear
    IE1.ExecWB 17, 0
    IE1.ExecWB 12, 2
    .Range("A1").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

 

[SQUIDD]

xenou

great, many thanks, i knew i need the end with but it was the dot, fantastic. I will test it.

dave

 

[SQUIDD]

xenou

Im am getting an error, object defined error, since i changed.

Any ideas

 

[xenou]

Not sure the IE1 object could be unset. What line does it error on?

 

[SQUIDD]

It errors here

.Range("A1").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False</pre>

 

[xenou]

Okay. I don't see Format as a valid parameter for PasteSpecial

Maybe try
.Range("A1").PasteSpecial

 

[xenou]

Actually, it appears you want the worksheet pastespecial method (not the Range pasteSpecial method). For this, the worksheet must be activated:

.Activate
.Range("A1").Activate
.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False

 

[SQUIDD]

xenou

Sorry for the delayed response.

For some reason, the new code is not pasting my entire data, something to do with paste area does not match in size.
After reading about activating, is it not the same a sheet selection?

I am trying to avoid selecting sheets as I read it can slow code down.

I thank you for your help.

Dave

 

[xenou]

THis is a case where you cannot avoid selection (unless you know for sure the sheet is active and you know for sure that you are at Cell A1 on that sheet (where you want to paste) -- as in your original code from post 1. I dont' know what the IE1 stuff is all about so that's a wildcard.