VBA Macro to copy entire row to another sheet

**malaasia18** · 02-11-2012, 09:11 AM

Hi I need your help here, please!

I have created a very basic VBA code for highlighting expiration date of the contract( please see the attached document). However, I need another option and I got stuck with it. I would like to copy these highlighted expired dates (including its whole row) into a new sheet, so-called Expired Contracts but not deleting these information from the original sheet (Sheet1). Sheet1 will be the main document which it is populated all the time and sometimes from time to time the highlighted expired contracts have to be copied into Expired Contracts sheet. I though that I could have in the Sheet1 a Button(Control Panel) which could action a copy upon its clicking. Can you help me out here , please ?

Regards,
Jo

**Simon Lloyd** · 02-11-2012, 09:25 AM

I've not looked at your example but assuming the highlighted row is the activerow this code will work

ActiveCell.EntireRow.Copy Destination:= Sheets("Sheet2").range("A" & rows.count).end(xlup).offset(1,0)

Hope that helps

Google Me

Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

**Richard Buttrey** · 02-11-2012, 09:38 AM

Hi,

Use a Data Filter.
Copy your field headers to your expired contracts sheet (say sheet2) and add the name 'DataOut' to this row of labels, add a criteria range with =NOW() in the second cell of the criteria range - the first is of course the label 'Expiry Date. Name thecriteria range say "crit", create a dynamic range for your sheet1 data, say 'data' and then add the following code.

Sheet1.Range("Data").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= Sheet2.Range("DataOut"), CriteriaRange:=sheet2.Range("crit")

**malaasia18** · 02-11-2012, 09:55 AM

Hi Simon,

Thank you for such quick response!
Unfortunately, sometimes I need to copy bunch of the data in one go ( not only the active one) but at least I know where to start. I will google it more

. Thank you!
Jo