Error on pastespecial

[-goss]

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

 

[dmoney]

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]

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




















- Show quoted text -

Thanks dmoney!
The error message is gone, but nothing is pasted into the target
workbook?
Also your code removed the wb.close so the kill command error'd out
since the file was open

I need to paste the data from the rip file into the target
workbook.worksheet to include formatting
As far as I can tell I can only key on the fact that the profit center
header is Arial Bold 9 as opposed to other header lines of different
fonts and sizes

Thanks!
goss