This is driving me crazy today!!!
How do I edit my code below in red to add paste special values (trying to keep formatting and values). Everything I tried so far gets me an error
Sub Export()
Dim wsSummary As Worksheet, wbNew As Workbook, wsNewSummary As Worksheet, wsNewData As Worksheet
Dim loDD_Data As ListObject
Dim i As Integer, lngInsertRow As Long
Dim PvtTable As PivotTable, lngStartColumn As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Source Summary Worksheet
Set wsSummary = ThisWorkbook.Worksheets("Summary")
'Source DD_Data table
Set loDD_Data = ThisWorkbook.Worksheets("AllData").ListObjects("DD_Data")
'Create a new workbook
Set wbNew = Workbooks.Add
'New Summary Worksheet
wbNew.Worksheets(1).Name = wsSummary.Name
Set wsNewSummary = wbNew.Worksheets(1)
'Delete any extra worksheets in the new workbook, if present
If wbNew.Worksheets.Count > 1 Then
For i = wbNew.Worksheets.Count To 2 Step -1
wbNew.Worksheets(i).Delete
Next
End If
'Copy the Job Summary Range
wsSummary.Range("rngJobSummary").Copy
'Paste to the new Summary worksheet
wsNewSummary.Cells(1, 1).PasteSpecial xlPasteAll
'Delete the data validation dropdown from cell B1 on the new summary worksheet
wsNewSummary.Cells(1, 2).Validation.Delete
How do I edit my code below in red to add paste special values (trying to keep formatting and values). Everything I tried so far gets me an error
Sub Export()
Dim wsSummary As Worksheet, wbNew As Workbook, wsNewSummary As Worksheet, wsNewData As Worksheet
Dim loDD_Data As ListObject
Dim i As Integer, lngInsertRow As Long
Dim PvtTable As PivotTable, lngStartColumn As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Source Summary Worksheet
Set wsSummary = ThisWorkbook.Worksheets("Summary")
'Source DD_Data table
Set loDD_Data = ThisWorkbook.Worksheets("AllData").ListObjects("DD_Data")
'Create a new workbook
Set wbNew = Workbooks.Add
'New Summary Worksheet
wbNew.Worksheets(1).Name = wsSummary.Name
Set wsNewSummary = wbNew.Worksheets(1)
'Delete any extra worksheets in the new workbook, if present
If wbNew.Worksheets.Count > 1 Then
For i = wbNew.Worksheets.Count To 2 Step -1
wbNew.Worksheets(i).Delete
Next
End If
'Copy the Job Summary Range
wsSummary.Range("rngJobSummary").Copy
'Paste to the new Summary worksheet
wsNewSummary.Cells(1, 1).PasteSpecial xlPasteAll
'Delete the data validation dropdown from cell B1 on the new summary worksheet
wsNewSummary.Cells(1, 2).Validation.Delete