Macro to perform query?

[BAQI]

I have two worksheets. I want to perform a query where it creates a single column with all the data from several columns from another sheet.

"T# Laser File" - This is the sheet I want the data to go to, deleting everything that was previously there, and entering the new data.
"DB - Printing Labels" - This is the sheet I want it to pull the data from.

The columns have headers, so I want it to ignore row 1. I want the macro to take all the data from Columns: a, i, j, and put it in column A on "T# Laser File" in order.
Example:

"DB - Printing Labels"
Row 1 has the following in rows A, I, J - 123456 XXX 001
Row 2- 9876 zzz 004
Row 5- 4567 CCCC 006

Will result in the following data in column A on sheet "T# Laser File":

123456
XXX
001
9876
zzz
004
4567
CCCC
006

I want it to ignore the row entirely if there is no data in column A of "DB - Printing Labels" for the corresponding row.

Is this possible?

 

[DanteAmor]

Hi @BAQI , thanks for posting on MrExcel board.

Try the following macro:

Sub perform_query()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  Set sh1 = Sheets("DB - Printing Labels")
  Set sh2 = Sheets("T# Laser File")
  
  a = sh1.Range("A2:J" & sh1.Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a, 1) * 3, 1 To 3)
  
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" Then
      b(k + 1, 1) = a(i, 1)
      b(k + 2, 1) = a(i, 9)
      b(k + 3, 1) = a(i, 10)
      k = k + 3
    End If
  Next
  sh2.Cells.ClearContents
  sh2.Range("A:A").NumberFormat = "@"
  sh2.Range("A1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[BAQI]

Hi @BAQI , thanks for posting on MrExcel board.

Try the following macro:

Sub perform_query()
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
 
  Set sh1 = Sheets("DB - Printing Labels")
  Set sh2 = Sheets("T# Laser File")
 
  a = sh1.Range("A2:J" & sh1.Range("A" & Rows.Count).End(3).Row).Value2
  ReDim b(1 To UBound(a, 1) * 3, 1 To 3)
 
  For i = 1 To UBound(a, 1)
    If a(i, 1) <> "" Then
      b(k + 1, 1) = a(i, 1)
      b(k + 2, 1) = a(i, 9)
      b(k + 3, 1) = a(i, 10)
      k = k + 3
    End If
  Next
  sh2.Cells.ClearContents
  sh2.Range("A:A").NumberFormat = "@"
  sh2.Range("A1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Wow! It worked perfectly. Thank you!