search for string, then copy multiple cells to new sheet

solidENM

Board Regular
Joined
Feb 23, 2017
Messages
87
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 acol i col ancol at
10121S1234
14.002.00
5.92
2.00
1.000.50
15.0010.42
10122S1235
8.500.25
6.58
2.50
1.001.00
9.5010.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"
 
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?

all the records that start with S are in column I. There are other records in column I for other departments that start with other letters. the other letters do not matter.
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
not sure if understood correctly the file format
try this and tell something, may not work

Code:
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

Hi Dsfobral, that worked great! It did copy other info from the col I (other departments, same format). Would it be easy to update this so that it only copies if Col I starts with "S" on the main tab?
 
Upvote 0
try like this

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


Cells(1, "I").Select


Do
    If Left(ActiveCell) = "S" Then
        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)
    Else
        ActiveCell.End(xlDown).Select
    End If
    
    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
 
Upvote 0
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).


Hi Rick,
the cells are all grouped together, but only the cells i mentioned have text. The other cells that are merged are blank. If i click on the cell, it highlights all of them. I can click on the letter column at the top to only highlight the column with data. I figured this didnt matter for vba since the cell with data is able to be copy/pasted.

This exported excel seems to be this way for printing purposes. dfsorbal has solved this though. Thanks guys. I should be able to figure out how to delete the other random data that got copied to the new sheet.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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