Solved: Copy then PasteSpecial a worksheet?

[DaveK]

This Forum has been very helpful, and time for another request for help!

All I am simply trying to do... is COPY the entire worksheet5 from workbook1, and PASTESPECIAL values into existing worksheet8 in existing workbook2.

HOWEVER, I cannot use the usual worksheet copy method, because it inserts a NEW worksheet into workbook2... and that causes me lots of problems... since I have 20 other worksheets in workbook2 which have REFERENCES to cells in this important worksheet8. If I delete the old worksheet8, for example, and rename the inserted copied sheet the desired sheetname of worksheet8... I LOSE ALL MY REFERENCES in all those other worksheets.

So, I have tried this:


Set SourceWS = SourceWB.Sheets("new CV Details")
SourceWS.Activate
Cells.Copy

Set NewFeedWorksheet = NewFeedWorkbook.Worksheets("CV Details")
NewFeedWorksheet.Activate
Cells.Clear
Cells.PasteSpecial xlPasteValues

Well, that last PasteSpecial does NOT work.

So I tried this:

Set SourceWS = SourceWB.Sheets("new CV Details")
SourceWS.Activate
With SourceWS
.Copy
End With

Set NewFeedWorksheet = NewFeedWorkbook.Worksheets("CV Details")
NewFeedWorksheet.Activate
With NewFeedWorksheet
.PasteSpecial Paste:=xlPasteValues
' .PasteSpecial xlPasteValues
End With


That does not work either.

So, this seems like such a simple thing to do... and I can't get it working?!
I've tried other combinations also, but finally asking you experts!

THANKS!
Dave :banghead:

[Bob Phillips]

Set SourceWS = SourceWB.Sheets("new CV Details")
Set NewFeedWorksheet = NewFeedWorkbook.Worksheets("CV Details")
SourceWS.Cells.Copy NewFeedWorksheet.Range("A1")
NewFeedWorksheet.Cells.Value = NewFeedWorksheet.Cells.Value

[DaveK]

Perfect. Thank You so much!

I've made a note of this way of doing a PasteSpecialValues.

Thanks again.
Dave