Raani has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Copy specific cells in each row of data with criteria to another
Hi
In Sheet1 I have data that is categorised into 'confirmed'and'pending' orders from columns A to J. I want a code that will go through each row of data and if the order is 'confirmed'it will copy the data in columns C:F of that row and paste it into a specific section of Sheet2 (starting from B72:G72)
I need it to go through each row to the end of the data and paste into each next available row in the section of Sheet 2.
How do i go about this?
Let me know if you need more information
Thanks you!
Raani
RE: Copy specific cells in each row of data with criteria to ano
Hello Raani,
Thank you for your post. It would be very helpful if you could email an extract from your worksheet to the following address, so that I can see the exact data and layout. This will help me tailor the code for your data.
forum@stl-training.co.uk
Many thanks
Marius Barnard
Excel Trainer
RE: Copy specific cells in each row of data with criteria to ano
Hi Raani,
Here is some code which filters by "Confirmed", copies the results, then pastes it in the "2016" sheet, automatically finding the next empty row.
You can modify parts of the code to use for getting the "Pending" results and paste into the relevant area in "2016".
Sub Confirmed()
Sheets("Status").Activate
Range("a1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter _ Field:=2, Criteria1:="Confirmed"
Range("c2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("2016").Activate
Range("d76").End(xlDown).Offset(1).Select
ActiveSheet.Paste
End Sub
I hope this helps!
Kind regards
Marius
RE: Copy specific cells in each row of data with criteria to ano
Hi Marius
Thank you!
The only issue I have is that I need the data in columns C:F of the Status tab to be pasted into the specified cells in the '2016' tab, at the moment data from column G of 'Status' is also being pasted into the narrow column H of '2016'. Also there will normally be data in columns H:S on the Status tab which would need to be excluded too- sorry I didn't mention that.
Is there a way to do this?
Thanks
Raani
RE: Copy specific cells in each row of data with criteria to ano
Hi Raani,
Here is the macro with extra code which inserts a blank column after column F so that it only processes the data up to column F. Afterwards,it deletes the empty column again.
Sub Confirmed()
Sheets("Status").Activate
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("a1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter _ Field:=2, Criteria1:="Confirmed"
Range("c2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("2016").Activate
Range("d76").End(xlDown).Offset(1).Select
ActiveSheet.Paste
Sheets("Status").Activate
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
End Sub
Kind regards
Marius
