Hi guys,
I followed this VBA code in youtube but I keep getting an error I am going crazy
Issue:
- Run-time error '1004':
- Method 'ShowAllData' of object'_Worksheet' failed
- Data does not copy to a new workbook
- Does not automatically save
Expected Outcome:
- Filter unique values from the table (Cell F2 on Sheet 1)
- Copy those values to a new workbook
- Automatically save them with a customized file name
- Summary: When running VBA, the value on Cell F2 should be filtered (and shown), copied, and pasted on a new workbook, then saved with a custom file name. After running the VBA, the filter should reset (show everything)
Code Used:
Code
Sub filter_copy_paste_save()
Dim region As String
Dim raw As Worksheet
Dim out As Worksheet
Dim count_col As Integer
Dim count_row As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set raw = ThisWorkbook.Sheets("Raw Data")
Set out = ThisWorkbook.Sheets("Output")
region = raw.Range("F2").Text
'clear pervious data
out.Cells.ClearContents
'determine the size of the range
raw.Activate
count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown))) + 3
'filter data on Raw Data tab
raw.Range("A4").AutoFilter field:=2, Criteria1:=region
'copy/paste to Output tab
raw.Range(Cells(4, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
out.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
'show data and remove filter
With raw
.ShowAllData
.AutoFilterMode = False
End With
'formatting Output tab
With out
.Activate
.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("A1").Select
.Copy
End With
'save and close the workbook
ActiveWorkbook.SaveAs Filename:="C:\Users\TempMichael\OneDrive - Cymax Stores Inc\Documents\Test Mails\" & _
"Region Report - " & region & ".xlsx"
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub____________
I attached the file where I used the code for reference
Thank you!!
