G
-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
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