Hello All,
I'm still quite new to VBA, but having some fun recently trying to prepare a tool for automation of reports that I need to prepare on regular basis.
What I'm trying to figure out now is: how to filter area on Sheet1, if area is not empty then copy range to another sheet, if empty - remove filter and go to next part. Offset is needed on both sheets as I don't want to copy first row from Sheet1 and there can be already some data pasted to Sheet2. This is what I have right now:
I've already tired several solutions but none of them was working fully properly. Would appreciate any help on this, thanks!
I'm still quite new to VBA, but having some fun recently trying to prepare a tool for automation of reports that I need to prepare on regular basis.
What I'm trying to figure out now is: how to filter area on Sheet1, if area is not empty then copy range to another sheet, if empty - remove filter and go to next part. Offset is needed on both sheets as I don't want to copy first row from Sheet1 and there can be already some data pasted to Sheet2. This is what I have right now:
VBA Code:
dim wsR as Worksheet: wsR = Workbooks.Sheets(1)
dim wsDel as Worksheets: wsDel =Workbooks.Sheets(2)
dim rDel as Long: rDel = wsDel.Range("a1").currentregion.rows.count
dim cSoure as Long: cSource = wsR.Cells.Find("source").column
ws.Range(("a1"), ws.Range("A1").end(xlToRight)).AutoFilter Field:=CSource, Criteria1:="=*AA*"
If Not IsEmpty(wsR.Range("a1").Offset(1, 0)) = true then
wsR.Range(("A1"), wsR.Range("A1").end(xlToRight).end(xlDown).Offset(1, 0).Copy 'offset to avoid copying header
wsDel.Range("A1").Offset(rDel, 0).PasteSpecial Paste:=xlPasteValues
wsR.Range(("A1"), wsR.Range("A1").end(xlToRight).end(xlDown).Offset(1, 0).delete Shift:=xlup 'in some cases I need to remove data from sheet1
ws.Range(("a1"), ws.Range("A1").end(xlToRight)).AutoFilter
else
ws.Range(("a1"), ws.Range("A1").end(xlToRight)).AutoFilter
end if
I've already tired several solutions but none of them was working fully properly. Would appreciate any help on this, thanks!