I'm trying to adjust a macro that was using .Select & Pasting pictures, but was throwing a fair amount of Run-time '1004' errors when trying to execute with an RPA tool. I know it's not the cleanest/most efficient, but I'm currently more concerned with the inconsistent results I'm getting. Some companies are correctly populating all the data in every cell, others have #REF ! in all, and some have #REF ! in only some cells. I'm not sure how to correct this.
Sample below - The first part of the code filters a pivot table from one workbook, pastes it to another after clearing existing data. The second part grabs data from the same workbook and pastes the data to another sheet. I need to maintain the formatting so xlPasteValues is not an option even though it fixes the #REF ! issues.
Sample below - The first part of the code filters a pivot table from one workbook, pastes it to another after clearing existing data. The second part grabs data from the same workbook and pastes the data to another sheet. I need to maintain the formatting so xlPasteValues is not an option even though it fixes the #REF ! issues.
Code:
Windows("CB2.xlsx").Activate
ActiveWorkbook.SlicerCaches("Slicer_Property").ClearManualFilter
For Each Sl_I In ActiveWorkbook.SlicerCaches("Slicer_Property").SlicerItems
' Add "Not" after "If" below if you do not want the indicated item to appear
If Not Sl_I.Value Like "xxxx*" Then Sl_I.Selected = False
Next
'Clear Existing Data
Workbooks("CB.xlsm").Worksheets("xxxx").Cells.ClearContents
'Copy and PasteSpecial between workbooks
Workbooks("CB2.xlsx").Worksheets("Sheet1").Range("A3:E500").Copy
Workbooks("CB.xlsm").Worksheets("xxxx").Range("A35").PasteSpecial Paste:=xlPasteAll
'Paste in the target destination
Workbooks("CB.xlsm").Worksheets("ClosingBell").Range("A333:H362").Copy
Workbooks("CB.xlsm").Worksheets("xxxx").Range("A1").PasteSpecial Paste:=xlPasteAll
'Activate the destination worksheet
Workbooks("CB.xlsm").Worksheets("xxxx").Activate