alantse2010
New Member
- Joined
- Jun 9, 2018
- Messages
- 31
- Office Version
- 365
- 2019
- 2016
- 2010
- Platform
- Windows
Hi Everyone.
I am trying to use the VBA to filter the data then copy from one worksheet to new worksheet. However, how can I copy the specific range (i.e.: A100:E100) if no filter result return?
I want the result look like this:
https://imgur.com/a/YSOEX42
Thank you very much for your help.
I am trying to use the VBA to filter the data then copy from one worksheet to new worksheet. However, how can I copy the specific range (i.e.: A100:E100) if no filter result return?
Code:
Dim wSheetStart As Worksheet
Dim wsDest As Worksheet
Set wSheetStart = ThisWorkbook.Sheets("ATA")
Application.ScreenUpdating = False
Set wsDest = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
wsDest.Name = "New report" & Date
wSheetStart.Activate
wSheetStart.AutoFilterMode = False
ActiveSheet.Range("A6:AC6").AutoFilter Field:=1, Criteria1:=">=" & DateSerial(Year(Now - 3), Month(Now - 3), Day(Now - 3)), Operator:=xlAnd, Criteria2:="<=" & DateSerial(Year(Now - 3), Month(Now - 3), Day(Now - 3))
Worksheets("ATA").Range("A6:N6").Select
'Worksheets("ATA").Range(Selection, Selection.End(xlToRight)).Select
Worksheets("ATA").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
wsDest.Activate
wsDest.Range("A1").Value = "(" & Format(Date - 3, "dd-mm-yyyy") & ")"
wsDest.Range("A2").Value = "ATA" & wsDest.Range("A1").Value
'wsDest.Range("A2:N2").Merge
wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
wSheetStart.Activate
wSheetStart.AutoFilterMode = False
ActiveSheet.Range("A6:AC6").AutoFilter Field:=1, Criteria1:=">=" & DateSerial(Year(Now - 2), Month(Now - 2), Day(Now - 2)), Operator:=xlAnd, Criteria2:="<=" & DateSerial(Year(Now - 2), Month(Now - 2), Day(Now - 2))
Worksheets("ATA").Range("A6:N6").Select
'Worksheets("ATA").Range(Selection, Selection.End(xlToRight)).Select
Worksheets("ATA").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
wsDest.Activate
wsDest.Range("A" & Rows.Count).End(xlUp).Offset(2).Value = "(" & Format(Date - 2, "dd-mm-yyyy") & ")"
wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = "ATA" & Format(Date - 2, "dd-mm-yyyy")
wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial
I want the result look like this:
https://imgur.com/a/YSOEX42
Thank you very much for your help.
Last edited: