VBA - Copy Filtered worksheet to another workbook

[gldurand]

Hi Guru's

I am using this code someone developed for me to copy active sheet in current workbook to another workbook.
It works perfectly when I don't have any filters. The minute use filters on the original worksheet and execute the code my copied file is completely unreadable

Can someone assist with modifying this code to take into consideration if I have filtered columns

Dim extension As String

extension = ".xlsx"

' Copy Active tab to another file

' Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.CopyObjectsWithCells = False ' Does not copy Macro objects

ActiveSheet.Copy
With ActiveSheet.UsedRange
.Value = .Value
.Validation.Delete ' Removes all Drop Down Lists
End With

Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyy-mm-dd") & " " & ActiveSheet.Range("B3") & extension
' wbNew.SaveAs ThisWorkbook.Path & " " & ActiveSheet.Range("B3") & extension
wbNew.Close True

Application.DisplayAlerts = True
Application.CopyObjectsWithCells = True
Application.ScreenUpdating = True

Any assistance would be greatly appreciated

 

[DanteAmor]

Hi @gldurand

Check this:

Sub COPY_SHEET()
  Dim extension As String
  Dim sh1 As Worksheet
  Dim wbNew As Workbook
 
  extension = ".xlsx"
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Application.CopyObjectsWithCells = False ' Does not copy Macro objects
 
  Set sh1 = ActiveSheet
 
  'create a new workbook with one sheet
  Set wbNew = Workbooks.Add(xlWBATWorksheet)
 
  ' Copy Active tab to another file
  sh1.Cells.Copy
  wbNew.Sheets(1).Range("A1").PasteSpecial xlPasteValues
  wbNew.Sheets(1).Range("A1").PasteSpecial xlPasteFormats
 
  wbNew.SaveAs ThisWorkbook.Path & "\" & Format(Date, "yyyy-mm-dd") & " " & sh1.Range("B3") & extension
  wbNew.Close False
 
  Application.DisplayAlerts = True
  Application.CopyObjectsWithCells = True
  Application.ScreenUpdating = True
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

 

[gldurand]

Thanks Dante.

Works like a charm!! Greatly appreciated Have a wonderful day