My macro won't copy values and formats

[Taffyb]

Hi,
I am trying to copy data from a closed workbook into another but the macroonly copies the data/values and does not copy the formats. I am using the following code to paste the data: PasteSpecial xlPasteValues - i have tried changing this to PasteSpecial xlPasteFormats and also PasteSpecial xlPasteAll and none of these will paste the formats Am i doing someething wrong?

Any help would be greatly appreciated.

Thanks,
Taffy B

[Norie]

Can you post the code?

Using xlPasteValue will only paste value, but xlPasteAll should paste values, formats etc

[Taffyb]

Can you post the code?

Using xlPasteValue will only paste value, but xlPasteAll should paste values, formats etc

here is the code I am trying to use - it is a copy of some code i saw on forum :
Sub GetLocalFile()
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
GetRange "C:\Documents and Settings\anthony.barker\Desktop", "TONY B Copy Of Trainers Availability 2013.xls", _
"Schedule", "A1:EZ12", _
Sheets("Schedule").Range("A1")

On Error GoTo 0
Application.ScreenUpdating = True

End Sub


'Main macro


Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

'Go to the destination range
Application.Goto DestRange

'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
Application.CutCopyMode = False
End With
End Sub

[Norie]

That code doesn't really copy and paste from the closed workbook.

It creates formulas/links to the closed workbook then copies those formulas and pastes their values.

If you want to copy with formats you'll need to open the workbook.

That would only take a few lines of code.

PS Can you add code tags to the code? It's hard to tell when one sub ends and the other begins.

[Taffyb]

Many thanks sfor the swift response - sorry for not using tags. I will try again but following your suggestion re opening the file instead.

Taffy b