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

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

On Oct 2, 8:43*am, dmoney wrote:
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- Hide quoted text -

- 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