If certain value in cell then copy full row to another sheet

**excelgirl84** · 08-24-2022, 03:54 PM

Hello,
I am trying to figure out a VBA code that will copy a row to another sheet if the value in A is = to a certain number. I am trying to automate this as the real sheet has over 100 rows and manually moving them takes a very long time.

I created a mocked up sample, see attached.

Looking at the mocked up file, Sheet "Main" -
If any cells in column A is = to 1234 and 1235, I would need a new sheet created called "Group1" and the Row/s would need to be copied into that sheet along with the headers
If any cells are = to 1589, I would need a new sheet created called "Group2" and the row/s would have to be copied over into that sheet. along with the headers

Any help would be greatly appreciated.

**nimrod1313** · 08-24-2022, 05:05 PM

Please Login or Register  to view this content.

ASSUMPTIONS:
Source sheet is activesheet
sample sheet provided is accurate in columns used
target numbers are constant numbers located in column A of activesheet

if this code has helped you please consider clicking "add reputation" , thx

**rollis13** · 08-24-2022, 05:34 PM

Hi to all.
Oh well, I'm late. Anyway, here is what I came up with:

Please Login or Register  to view this content.

**excelgirl84** · 08-24-2022, 05:55 PM

Sorry, I think I made the mistake of putting the other sheets in there to start with. Technically, I would like the other sheets "group1" and "group2" to be created with the code along with the header row from the "Main" Sheet too. Not sure why I put them in there. Sorry about that! Super new to all this

is there a way to edit your code to have the sheets created?

**nimrod1313** · 08-24-2022, 05:58 PM

VERSION 2 .. creates sheets if do not exist

Please Login or Register  to view this content.

if this code has helped you please consider clicking "add reputation" , thx

**excelgirl84** · 08-24-2022, 06:01 PM

Wow! Works fantastic!! Thank you so much.

I really appreciate all the fast help with this.

**nimrod1313** · 08-24-2022, 06:11 PM

Hello ExcelGirl84
Just curious, who's solution did you end up going with ?

**excelgirl84** · 08-24-2022, 06:31 PM

I'm guessing if i need to add more "numbers" and groups
I would just do this?

For Each num In Columns("A:A").SpecialCells(xlCellTypeConstants, 1)
If num.Value = 1234 Or num.Value = 1235 Then
addToArray Group1, Columns("A:M"), num.Row
End If
If num.Value = 1589 Then
addToArray group2, Columns("A:M"), num.Row
End If
If num.Value = 1111 Then
addToArray group3, Columns("A:M"), num.Row
End If
If num.Value = 2222 Then
addToArray group4, Columns("A:M"), num.Row
End If

and so on?