Copying Cells From One Sheet To Another Based On Certain Criteria

M

mlarson

Well-known Member
Joined
Aug 25, 2011
Messages
509
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I hope you can help me with this question. I'm not sure how to post an Excel spreadsheet but it may not be needed for this question anyway. Thanks in advance for your help!

The spreadsheet would show Column A cells with either the word "yes" or "no." Same with Column B, C, D...M. Each column goes down 350 rows.

Here are the actions I'd like to be able to accomplish...

First Group:
On "Sheet1", find the first "yes" that appears (going from top to bottom) in Column A.
Copy cells from columns P-AH on that same row the first "yes" appears.
Paste those cells on "Sheet2" in B1-T1.
On "Sheet1", find the first "yes" that appears (going from top to bottom) in Column B.
Copy cells from columns P-AH on that same row the first "yes" appears.
Paste those cells on "Sheet2" in B2-T2.
Repeat for columns C, D, E...M (first "yes" in each column, so Column M results will go to B13-T13).

Second Group:
On "Sheet1", find the second "yes" that appears (going from top to bottom) in Column A.
Copy cells from columns P-AH on that same row the second "yes" appears.
Paste those cells on "Sheet2" in B14-T14.
On "Sheet1", find the first "yes" that appears (going from top to bottom) in Column B.
Copy cells from columns P-AH on that same row the first "yes" appears.
Paste those cells on "Sheet2" in B26-T26.
Repeat for columns C, D, E...M with the third "yes" for each.

Third Group:
On "Sheet1", find the fourth "yes" that appears (going from top to bottom) in Column A.
Copy cells from columns P-AH on that same row the fourth "yes" appears.
Paste those cells on "Sheet2" in B27-T27.
On "Sheet1", find the fifth "yes" that appears (going from top to bottom) in Column B.
Copy cells from columns P-AH on that same row the fifth "yes" appears.
Paste those cells on "Sheet2" in B39-T39.
Repeat for columns C, D, E...M with the second "yes" for each.

In total there will be 39 rows of data entered on "Sheet2."

Thanks again!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
How about
Code: 
Sub copyData()

   Dim Cl As Range
   Dim Col As Long
   Dim Cnt As Long
   Dim Rw As Long
   Dim Ary As Variant
   
   With Sheets("sheet1")
      For Col = 1 To 13
         Cnt = 0
         Rw = Col
         If Col = 1 Then
            Ary = Array(1, 2, 4)
         ElseIf Col = 2 Then
            Ary = Array(1, 1, 5)
         Else
            Ary = Array(1, 3, 2)
         End If
         .Columns(Col).AutoFilter 1, "Yes"
         For Each Cl In .Range(.Cells(2, Col), .Cells(Rows.Count, Col).End(xlUp)).SpecialCells(xlVisible)
            Cnt = Cnt + 1
            If Cnt = Ary(0) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Range("B" & Rw)
               Rw = Rw + 13
            End If
            If Cnt = Ary(1) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Range("B" & Rw)
               Rw = Rw + 13
            End If
            If Cnt = Ary(2) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Range("B" & Rw)
               Rw = Rw + 13
            End If
            If Cnt = WorksheetFunction.Max(Ary) Then Exit For
         Next Cl
         .AutoFilterMode = False
      Next Col
   End With
   
End Sub
 
Upvote 0
Fluff, thank you, that worked well! Is it possible to modify the code to have it include all permutations of first thru fifth? Hopefully without typing each specific permutation? For example:
1, 1, 1, 1,...1
1, 2, 1, 1,...1
1, 3, 1, 1,...1
2, 1, 1, 1,...1
2, 2, 1, 1,...1

I'm a novice with this and your help is much appreciated!
 
Upvote 0
Not sure I understand you, could you re-explain?
 
Upvote 0
Sure. My apologies for not being clearer. What I mean is every possible combination of the numbers in parentheses below. So Array (1, 1, 1), Array (1, 1, 2), Array (1, 1, 3), Array (1, 1, 4), Array (1, 1, 5), Array (1, 2, 1), Array (1, 2, 2), Array (1, 2, 3) so on and so forth. I hope the way I explained it makes sense.

If Col = 1 Then
Ary = Array(1, 2, 4)
ElseIf Col = 2 Then
Ary = Array(1, 1, 5)
Else
Ary = Array(1, 3, 2)
 
Upvote 0
Unfortunately I still don't understand.
What is happening at the moment is
Code: 
         If Col = 1 Then
            Ary = Array([COLOR=#ff0000]1[/COLOR], [COLOR=#0000ff]2[/COLOR], [COLOR=#ff8c00]4[/COLOR]) ' For col A copy [COLOR=#ff0000]1st [/COLOR]"yes" to row 1, [COLOR=#0000ff]2nd [/COLOR]"yes" to row 14 & [COLOR=#ff8c00]4th [/COLOR]"Yes" to row 27
         ElseIf Col = 2 Then
            Ary = Array([COLOR=#ff0000]1[/COLOR], [COLOR=#0000ff]1[/COLOR], [COLOR=#ff8c00]5[/COLOR]) ' For col B copy [COLOR=#ff0000]1st [/COLOR]"yes" to row 2, [COLOR=#0000ff]1st [/COLOR]"yes" to row 15 & [COLOR=#ff8c00]5th [/COLOR]"Yes" to row 28
         Else
            Ary = Array([COLOR=#ff0000]1[/COLOR], [COLOR=#0000ff]3[/COLOR], [COLOR=#ff8c00]2[/COLOR]) ' For all other cols copy [COLOR=#ff0000]1st [/COLOR]"yes" to row 3,4 etc, [COLOR=#0000ff]3rd [/COLOR]"yes" to row 16,17 etc & [COLOR=#ff8c00]2nd [/COLOR]"Yes" to row 29,30 etc
         End If
How would you like to change that?
 
Upvote 0
In the array, the first (1), second(2), and third(3) "yes" in the column noted (C, E, or E) of all possible combinations, like below. This would be for only 3 columns and could be easily typed in given that it's only 29 different combinations. I'm curious if this could be done for, say, the first, second, third, fourth, and fifth "yes" in each column C-M (11 columns). It would be a ton of combinations and would be nearly impossible to hand-type as a code, so I'm curious if there is a better formula/code for it? Thank you again for all your time on this, Fluffy!
C D E
1 1 1
1 1 2
1 1 3
1 2 1
1 2 2
1 2 3
1 3 1
1 3 2
1 3 3
2 1 1
2 1 2
2 1 3
2 2 1
2 2 2
2 2 3
2 3 1
2 3 2
2 3 3
3 1 1
3 1 2
3 1 3
3 2 1
3 2 2
3 2 3
3 3 1
3 3 2
3 3 3
 
Upvote 0
Is this a totally new macro that you want, as it bares no resemblance to your OP?
 
Upvote 0
In a way, yes. It is similar that it is looking for "yes" at various spots, but it is different as this second one is larger in scope.
 
Upvote 0
In that case I would recommend starting a new thread.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Help identifying whether Sheet1!A* = Sheet2!A* and then populating with Sheet2!C*
Replies
3
Views
294
kevin9999
K
T
  • Solved
Copy and paste based on sheet name and cell value
Replies
4
Views
376
skillilea
S
P
  • Question
VBA to Copy range from Sheet to another and defined a name
Replies
1
Views
278
Persl
P
D
  • Question
Data from one sheet to overide data on another sheet
Replies
0
Views
177
Daroh
D
A
  • Solved
copy paste from one worksheet to other but in different columns
2
Replies
18
Views
703
Joe4
Joe4

Forum statistics

Threads
1,214,625
Messages
6,120,598
Members
448,973
Latest member
ksonnia

Share this page

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