Too much .activate

Hello

My goal is to create a code that "exports" 8 different pivottables from 4 different worksheets to another workbook called rev*.
The filename of the rev* workbook changes name each week but the first 3 characters are always the same.
The code below is a partial code to do the first 2 tables from worksheet 1.
When updating, the code needs to clear the old data because the pivot tables can vary in size each week.

Please Login or Register  to view this content.

My questions is this, is there a better way to do the select worksheet and paste? I think that my current code is quite messy. And why do I have to activate both the workbook and the worksheet like this?
swb.Activate
With Worksheets("1")
.Activate
.Range("B4").Select
End With
I tried to just do dwb.worksheets("1").activate but get an error.

The Code works. Would appreciate some input though if there is something smarter than my current code as I didn't know what VBA was prior to january, so I'm still pretty new at this. Let me know if you have additional questions.

Firstly, could you please edit your post to add code tags - makes it a lot easier to read/copy.

Don't use Activate or Select.

If you want to copy 'PivotTable2' from worksheet '1' in the workbook referenced by dwb and paste it to worksheet '1' in workbook swb at B4.

Please Login or Register  to view this content.

Not sure how copy and paste would clear cells.

If you wanted to clear cells you would use Clear (or ClearContents), or even just set them all = "".

Paste and PasteSpecial aren't the same.

If you want to clear all formats from a range use ClearFormats instead of resetting individual format properties like Interior.