Copy an entire column to another sheet based on multiple cell value in that column

rehan kazi · Aug 6th 2021

Hello everyone,

I have a spreadsheet that has columns of info. I would like to copy columns into other sheets, based on the multiple cell criteria. I have no idea how to perform this. Can someone help me please? thanks in advance for any help!!

Code

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AF10")
Sheet1.Range("3:3") = Sheet2.Range("AF11")

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AG10")
Sheet1.Range("3:3") = Sheet2.Range("AF11")



Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AI10")
Sheet1.Range("3:3") = Sheet2.Range("AI11")

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AJ10")
Sheet1.Range("3:3") = Sheet2.Range("AI11")



Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AL10")
Sheet1.Range("3:3") = Sheet2.Range("AL11")

Sheet1.Range("1:1") = Sheet2.Range("G11")
Sheet1.Range("2:2") = Sheet2.Range("AM10")
Sheet1.Range("3:3") = Sheet2.Range("AL11")

'If above criteria met then copy entire column from sheet1 and paste to sheet3

Display More

jolivanes · Aug 7th 2021

When in Sheet1, run this macro and have a look at what is selected.

Code

Sub AAAAA()
Sheet1.Range("1:1").Select
End Sub

After that, go to Sheet2 and run this macro and see what is selected.

Code

Sub AAAAA()
Sheet2.Range("G11").Select
End Sub

Is the two selections what you want to compare?

If not, let us know what you want to do by accurately describing, not code that does not work, what you want to achieve.

I have not downloaded your file. I try to stay away from doing that. Been unpleasantly surprised too many times.

rehan kazi · Aug 8th 2021

Hello jolivanes!

Thank you for the consideration…

I have three Headers in sheet1 ......"1:1”,"2:2" & "3:3"

Compare Sheet2 "G11" in sheet1 "1:1"

Compare Sheet2 "AF10" in sheet1 "2:2"

Compare Sheet2 "AF11" in sheet1 "3:3"

Sheet2 cell value "G11", "AF10" and "AF11"........ If all matches in any single column of the Sheet1 header, copy that entire column with two more offset columns and also column B that is Date column......... and paste it onto Sheet 3.

Please have a look attached file; I have given an example and marked the matching criteria in sheet 1 and also copied the full expected result in sheet 3.

I hope this brief explanation make any sense.

**KjBox** · Aug 8th 2021

In your sample workbook why is Sheet1 cells F1 and F3 not a match with cells I1 and I3, but cells I1 and I3 is a match with cells O1 and O3?

Cells F1 and F3 are exactly the same as cells O1 and O3

rehan kazi · Aug 8th 2021

Basically there are total 6 criteria’s in sheet2, to be match with sheet1.

The contents of the sheet 1 column header are fixed and the criteria’s in sheet 2 (cell contents) are always changing.

If the 3 criteria (e.g G11, AF10 & AF11) from sheet 2 match all together in a single column of sheet 1, then copy the whole column with 2 more offset columns.

The following criteria are currently matched in the attached Excel.

1- Sheet2 G11, AF10 & AF11 matches with Sheet1 column 9 (i.e “I”)

2- Sheet2 G11, AG10 & AF11 matches with Sheet1 column 15 (i.e “O”)

3- Sheet2 G11, AI10 & AI11 matches with Sheet1 column 30 (i.e “AD”)

4- Sheet2 G11, AJ10 & AI11 matches with Sheet1 column 33 (i.e “AG”)

5- Sheet2 G11, AL10 & AL11 matches with Sheet1 column 30 (i.e “AD”)

6- Sheet2 G11, AM10 & AL11 matches with Sheet1 column 33 (i.e “AG”)

rehan kazi · Aug 8th 2021

corrected..

5- Sheet2 G11, AL10 & AL11 matches with Sheet1 column 42 (i.e “AP”)

6- Sheet2 G11, AM10 & AL11 matches with Sheet1 column 51 (i.e “AY”)

**KjBox** · Aug 8th 2021

OK, that makes more sense now, I was looking for the matches wrongly.

rehan kazi · Aug 8th 2021

...

rehan kazi · Aug 14th 2021

Hello Everyone....

I've created a code to extract the data if any one header matches. Code working fine. However, it is also extracting three unwanted columns in sheet3 columnA to columnC. I was trying to stop extraction of unwanted column. Any help would be greatly appreciated.

Code

Private Sub CommandButton1_Click()
 Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, a() As Variant, b() As Variant
  Dim i As Long, j As Long, lr As Long, lc As Long
  Set sh1 = Sheets("Sheet1")          'origin
  Set sh2 = Sheets("Sheet3")  'destination
  Set sh3 = Sheets("Sheet2")
  
  sh2.Rows("11:" & Rows.Count).ClearContents
   
      If sh3.Range("AF10") <> "" Or sh3.Range("AG10") <> "" Or sh3.Range("AI10") <> "" Or sh3.Range("AJ10") <> "" Or sh3.Range("AL10") <> "" Or sh3.Range("AM10") <> "" Then
          
With sh3
        
        sh2.Range("F10").Value = .Range("AF10").Value
        sh2.Range("I10").Value = .Range("AG10").Value
        sh2.Range("L10").Value = .Range("AI10").Value
        sh2.Range("O10").Value = .Range("AJ10").Value
        sh2.Range("R10").Value = .Range("AL10").Value
        sh2.Range("U10").Value = .Range("AM10").Value
       
    End With
  
  lr = sh1.Range("C" & Rows.Count).End(xlUp).Row
 
  lc = sh1.Cells(2, Columns.Count).End(xlToLeft).Column
  a = WorksheetFunction.Transpose(sh1.Range("A2", sh1.Cells(2, lc)).Value)
 
  lc = sh2.Cells(10, Columns.Count).End(xlToLeft).Column
  b = WorksheetFunction.Transpose(sh2.Range("A10", sh2.Cells(10, lc)).Value)
  

    
  For i = 1 To UBound(a, 1)
    For j = 1 To UBound(b, 1)
      If b(j, 1) = a(i, 1) Then
         sh2.Cells(11, 5).Resize(lr).Value = sh1.Cells(6, 2).Resize(lr).Value
         sh2.Cells(11, j).Resize(lr).Value = sh1.Cells(6, i).Resize(lr).Value
          sh2.Cells(11, j + 1).Resize(lr).Value = sh1.Cells(6, i + 1).Resize(lr).Value
          sh2.Cells(11, j + 2).Resize(lr).Value = sh1.Cells(6, i + 2).Resize(lr).Value
         sh2.Activate
        Exit For
    
      End If
    Next
  Next
  Else
  MsgBox "No Data Found"
     End If
End Sub

Display More

Copy an entire column to another sheet based on multiple cell value in that column

Participate now!

Share