Paste Special between Spreadsheets

[Meryl Miller]

Hi -

I am automating some plan data that we have and seem to be running into a
problem using paste special to copy data from one spreadsheet to another. Is
there any restriction that dones not allow this? I have been able to
successfully write VBA code that copies and pastes the data that I need from
one spreadsheet to another - the only issue is that I would really rather
just past the values rather than the formulas as well (hence the need for
paste special). Does anyone have a simple example where they have gotten
this to work?

Thanks,
Meryl

 

[Meryl Miller]

Hi -

I figured it out. Please ignore the previous post.

- Meryl

 

[Kevin B]

The following code selects a contiguous block of cells in Sheet 1 of the
currently active workbook, copies the selection, opens a new workbook, does a
paste special and saves the new workbook.

Sub CopyPSpec()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues
ActiveWorkbook.Save

Set ws = Nothing
Set wb = Nothing
Exit Sub

End Sub