IF isempty then copy/paste with offset on filtered range

hptrz

New Member
Joined
Oct 9, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
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:

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!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi & welcome to MrExcel.
You seem to be filtering sheet ws, but you have not defined that anywhere, should it be wsR?
 
Upvote 0
Hi & welcome to MrExcel.
You seem to be filtering sheet ws, but you have not defined that anywhere, should it be wsR?

my mistake! below should be "correct" 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

wsR.Range(("a1"), wsR.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 but it looks like this line is working fine
wsR.Range(("a1"), wsR.Range("A1").end(xlToRight)).AutoFilter
else
wsR.Range(("a1"), wsR.Range("A1").end(xlToRight)).AutoFilter
end if
 
Upvote 0
How about
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

wsR.Range("a1").AutoFilter Field:=CSource, Criteria1:="=*AA*"
wsR.AutoFilter.Range.Offset(1).Copy
wsDel.Range("A1").Offset(rDel, 0).PasteSpecial Paste:=xlPasteValues
wsR.AutoFilter.Range.Offset(1).EntireRow.Delete
wsR.Range("a1").AutoFilter
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,724
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top