search for string, then copy multiple cells to new sheet

[solidENM]

Hello,
I am in need of a complex (to me) macro that will copy paste data from one sheet to a new tab. I am dealing with an excel file that has a ton of random columns that I want to remove by pasting the relevant data to a new tab. The worksheet is a timesheet record, and i want to compare the AN and AT columns in an easy to read format. There is a lot of junk in the other cells that I do not need.

I need to Search in column I, and copy all the cells that begin with "S" to a new page. I will also need the data copied over in columns AN, At, and A. There is accompanying data in other cells. the columns are fixed but some have more rows than others.

Data needed copied to new sheet.
Col I: customer
col A: serial
Column an: number
Column at: number

the data in columns AN and AT will be on the same row. The constant I found is that there will be at least 2 blank spaces after this number in col AN, but there will be random numbers above it. the amount of names vary based on how many people worked on whichever project. Below is a sample for reference.

col a	col i		col an	col at
10121	S1234
		14.00	2.00
				5.92
				2.00
		1.00	0.50
			15.00	10.42
10122	S1235
		8.50	0.25
				6.58
				2.50
		1.00	1.00
			9.50	10.33

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

in the example above, i would need A1, I1, AN8 and AT8 copied to a new tab. For the second list, it would be A11, I11, an19, AT19.

I would like them to be pasted as a1, b1, c1, d1 on the new tab. then looping around to get all the data from the first tab to the second for all col I cells that begin with "S"

 

[Rick Rothstein]

What is in Column AT... constants or formulas?

 

[dfsobral]

in columns A and I, do all records matter? meaning in the row they all star with "s" in column I?

 

[solidENM]

What is in Column AT... constants or formulas?

Hi Rick,
the entire sheet is raw data, no formulas. It is an exported file from another program. There are a lot of merged cells which has made it very difficult to read in its current format.

Column AN is the sum of estimated hours. Column AT is the sum of actual hours.

 

[Rick Rothstein]

There are a lot of merged cells which has made it very difficult to read in its current format.

Are any of the cells in Columns A, I, AN or AT merged?

 

[solidENM]

in columns A and I, do all records matter? meaning in the row they all star with "s" in column I?

Hello,
all of the records that start with S matter. There are other columns which have other letters, but I am only concerned with the ones that begin with S.

 

[solidENM]

Are any of the cells in Columns A, I, AN or AT merged?

Hi Rick,
all 4 of these columns are merged. If that is a problem, I was able to export into another filetype that does not have merged cells. In that secondary sheet, the constant I found is that the number of estimated hours is a numerical cell below a text cell. It is a totally different format.

secondary excel file format is sorted as:
Col D: customer (d1)
col A: serial (a1)
Column A: number (A6)
Column B: number (b6)

10122			S1222
	MIKE
	MARK
	JOHN
	ED
14.00	8.00

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Would it be easier if i used this secondary format? I think the first format with the merged cells is exported so that its easily printed. I used that since I have dealt with finding blank cells as a constant before. But havent dealt with searching based on numerical/alphabetical changes in columns.

 

[Rick Rothstein]

I am not exactly clear on the layout of the unmerged version of the worksheet. Can you describe in what way the cells in Columns A, I, AN and AT are merged (what cells are involved in each merge)? Better yet would be if you could post a copy of your workbook (with the merged cells) to DropBox so that we can download a copy of it and try out our ideas on your actual data (instead of trying to recreated your data layout for ourselves).

 

[dfsobral]

Hello,
all of the records that start with S matter. There are other columns which have other letters, but I am only concerned with the ones that begin with S.

other columns?? aren't these records (that start with S) all in column I?
what i'd like to know was if all records in column I start with S or may be others that don't matter?

 

[dfsobral]

not sure if understood correctly the file format
try this and tell something, may not work

Sub subtest()
Dim arrA()
Dim arrI()
Dim arrAN()
Dim arrAT()
Dim x As Long


Cells(1, "I").Select


Do
    x = x + 1
    ReDim Preserve arrA(1 To x)
    arrA(x) = Cells(ActiveCell.Row, "A")
    ReDim Preserve arrI(1 To x)
    arrI(x) = Cells(ActiveCell.Row, "I")
    
    ActiveCell.End(xlDown).Select
    ReDim Preserve arrAN(1 To x)
    arrAN(x) = Cells(ActiveCell.Row, "AN").End(xlUp)
    ReDim Preserve arrAT(1 To x)
    arrAT(x) = Cells(ActiveCell.Row, "AT").End(xlUp)


    If IsEmpty(ActiveCell) Then GoTo Pasting
Loop


Pasting:
Sheets.Add
Range(Cells(1, 1), Cells(UBound(arrA), 1)).Select
Selection = Application.Transpose(arrA)
Range(Cells(1, 2), Cells(UBound(arrI), 2)) = Application.Transpose(arrI)
Range(Cells(1, 3), Cells(UBound(arrAN), 3)) = Application.Transpose(arrAN)
Range(Cells(1, 4), Cells(UBound(arrAT), 4)) = Application.Transpose(arrAT)


End Sub