give this a shot -- made a few minor changes.



Sub purch_GetProfitCenterData()

Dim wbBook As Workbook
Dim wbData As Workbook
Dim wsData As Worksheet
Dim wsPCData As Worksheet
Dim rngData As String
Dim strFile As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set wbBook = ThisWorkbook
Set wsData = wbBook.Worksheets("Centers")
rngData = wsData.Range("A1").Address


'Clear current Data
wsData.UsedRange.Clear

'Open the rip file and copy all data
strFile = "C:\FoodTrak\rip-PurchRecapByPC.xls"
Set wbData = Application.Workbooks.Open(strFile)
Set wsPCData = wbData.Worksheets(1)

'Paste data and formats
wsPCData.UsedRange.Copy
Range(rngData).PasteSpecial (xlPasteValuesAndNumberFormats)

'wsPCData.UsedRange.Copy rngData.PasteSpecial(xlPasteFormats)


'Delete the rip file
Kill strFile

'Cleanup
Set wbBook = Nothing
Set wbData = Nothing
Set wsData = Nothing
Set wsPCData = Nothing
'Set rngData = Nothing


With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With


End Sub

"-goss" wrote:

Hi all,

The code below returns error at

wsPCData.UsedRange.Copy
rngData.PasteSpecial(xlPasteValuesAndNumberFormats )

Here is the error message:
Run-timed error '1004':
Unable to get the pastespecial property of the range class

I am unsure as to why I am receiving the error

Many thanks!
-goss
================
================

Option Explicit

Sub purch_GetProfitCenterData()

Dim wbBook As Workbook
Dim wbData As Workbook
Dim wsData As Worksheet
Dim wsPCData As Worksheet
Dim rngData As Range
Dim strFile As String

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False

Set wbBook = ThisWorkbook
Set wsData = wbBook.Worksheets("Centers")
Set rngData = wsData.Range("A1")

'Clear current Data
wsData.UsedRange.Clear

'Open the rip file and copy all data
strFile = "C:\FoodTrak\rip-PurchRecapByPC.xls"
Set wbData = Application.Workbooks.Open(strFile)
Set wsPCData = wbData.Worksheets(1)

'Paste data and formats
wsPCData.UsedRange.Copy
rngData.PasteSpecial(xlPasteValuesAndNumberFormats )
wsPCData.UsedRange.Copy rngData.PasteSpecial(xlPasteFormats)


'Delete the rip file
Kill strFile

'Cleanup
Set wbBook = Nothing
Set wbData = Nothing
Set wsData = Nothing
Set wsPCData = Nothing
Set rngData = Nothing


With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With


End Sub