VBA paste special 'print range'

[dazfoz]

HI,

I have a macro that is used to copy a single worksheet from a workbook, then to save a copy of it with paste special values into a predefined destination.

All in running good, but the only problem is that when I open the values only copy, the page setup and print range is lost.

Is there anyway that I can copy this as well?

currently using;

With Sheets("Sheet1")
        .Range("A1").PasteSpecial Paste:=8
        .Range("A1").PasteSpecial Paste:=xlValues
        .Range("A1").PasteSpecial Paste:=xlFormats

Thanks in advance

Darren

 

[dazfoz]

Just thought I would expand on the code that is currently used;

Set Target = ActiveWorkbook
    targetFileName = ActiveWorkbook.Name

    Windows(sourceFileName).Activate
    Sheets("Teesside 106's").Cells.SpecialCells(xlVisible).Copy

    Windows(targetFileName).Activate

    With Sheets("Sheet1")
        .Range("A1").PasteSpecial Paste:=8
        .Range("A1").PasteSpecial Paste:=xlValues
        .Range("A1").PasteSpecial Paste:=xlFormats
    End With
    Application.CutCopyMode = False
    ActiveWorkbook.Close True