VBA search between blank rows for data and copy and paste

[ckdragon]

Hi team,

Wondering if it is possible to have a VBA to look in between blank rows and return specific cells in a copy paste function to another sheet.

I essentially need a VBA that will search between blank rows and return specific cells and copy them to another sheet

The paste format would be just into column A, B, C, D, E, F, G, H all on the same row starting in row 2.

I have highlighted the cells that need to be copied across for reference in the minisheet below.

The columns start at A row 2 (I dont know why that didnt copy across in the minisheet - sorry!)

Any help would be greatly appreciated.

THANK YOU

Truck 1	Community	3/28/22	10:36:58 AM	3/28/22	10:53:45 AM	Base 1	Stop	0:16:47	12.058	140483
Truck 1	Community	3/28/22	11:01:03 AM	3/28/22	11:50:35 AM	Stop	Stop	0:49:32	46.73	140529
Truck 1	Community	3/28/22	11:59:26 AM	3/28/22	12:09:47 PM	Stop	Stop	0:10:21	6.738	140536
Truck 1	Community	3/28/22	12:48:17 PM	3/28/22	12:59:53 PM	Stop	Stop	0:11:36	6.716	140543
Truck 1	Community	3/28/22	1:07:56 PM	3/28/22	1:26:54 PM	Stop	Stop	0:18:58	18.372	140561
Truck 1	Community	3/28/22	1:40:03 PM	3/28/22	2:01:16 PM	Stop	Stop	0:21:13	4.635	140566
Truck 1	Community	3/28/22	2:09:17 PM	3/28/22	2:32:37 PM	Stop	Base 1	0:23:20	18.153	140584
									113.402
Truck 1	Community	3/28/22	7:18:42 PM	3/28/22	7:56:35 PM	Base 1	Stop	0:37:53	34.283	140618
Truck 1	Community	3/28/22	10:24:50 PM	3/28/22	10:52:35 PM	Stop	Base 1	0:27:45	34.145	140652
									68.428
Truck 1	Community	3/29/22	8:58:36 AM	3/29/22	9:13:02 AM	Base 1	Stop	0:14:26	10.613	140663
Truck 1	Community	3/29/22	10:30:01 AM	3/29/22	10:42:32 AM	Stop	Stop	0:12:31	10.385	140673
Truck 1	Community	3/29/22	11:42:14 AM	3/29/22	11:46:54 AM	Stop	Base 1	0:04:40	1.935	140675
									22.933
Truck 1	Community	3/30/22	9:34:36 AM	3/30/22	9:35:33 AM	Base 1	Base 2	0:00:57	0.295	140676
									0.295
Truck 1	Community	3/30/22	9:46:18 AM	3/30/22	9:47:14 AM	Base 2	Base 1	0:00:56	0.323	140676
									0.323
Truck 1	Community	3/30/22	10:44:12 AM	3/30/22	10:55:36 AM	Base 1	Stop	0:11:24	9.554	140686
Truck 1	Community	3/30/22	11:15:58 AM	3/30/22	11:30:14 AM	Stop	Stop	0:14:16	13.02	140699
Truck 1	Community	3/30/22	12:30:01 PM	3/30/22	12:37:27 PM	Stop	Stop	0:07:26	4.715	140703
Truck 1	Community	3/30/22	12:55:26 PM	3/30/22	12:58:09 PM	Stop	Base 1	0:02:43	1.248	140705
									28.537

 

[Peter_SSs]

I dont know why that didnt copy across in the minisheet

Looks like you clicked 'Table Only' on the Xl2bb toolbar, not 'Mini Sheet'

Assuming that original data is on Sheet1 and you want the results on Sheet2, try this with a copy of your workbook.

Sub GetSectionData()
  Dim rA As Range
  
  For Each rA In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    With rA
      Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 8).Value = Array(.Cells(1, 1), .Cells(1, 3), .Cells(1, 4), _
        .Cells(.Rows.Count, 5), .Cells(.Rows.Count, 6), .Cells(1, 7), .Cells(.Rows.Count, 8), .Cells(.Rows.Count + 1, 10))
    End With
  Next rA
End Sub

 

[ckdragon]

I literally do not have enough words to thank you.

This works PERFECTLY.

Showed me some errors in my data too, which has now created more work for me but I would much rather know now.

THANK YOU AGAIN!

Looks like you clicked 'Table Only' on the Xl2bb toolbar, not 'Mini Sheet'

Assuming that original data is on Sheet1 and you want the results on Sheet2, try this with a copy of your workbook.

Sub GetSectionData()
  Dim rA As Range
 
  For Each rA In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    With rA
      Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 8).Value = Array(.Cells(1, 1), .Cells(1, 3), .Cells(1, 4), _
        .Cells(.Rows.Count, 5), .Cells(.Rows.Count, 6), .Cells(1, 7), .Cells(.Rows.Count, 8), .Cells(.Rows.Count + 1, 10))
    End With
  Next rA
End Sub

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.