use vb macro to find, copy, and paste into another sheet in excel 1

[lukn4it]

In the attachment you will find three invoices that are consecutive vertical text documents converted to excel. I need to automatically seperate each invoice into a seperate sheet. The only thing consistant between each invoice is they all start with the word "Bill" and end with a copyright. Is there a way to cut the invoice starting at "Bill" and ending at "copyright", paste it into another sheet and loop the process until all the invoices have been removed?

 

[xlbo]

yes - search col A, look for Bill. Then search col A again from where you found "Bill" and look for copyright. that is the rows you need to use

To save further confusion, here is some code to get you going. Where the msgbox is used, you need to insert some code to cut and paste the data to a new sheet. This assumes that "Copyright" will be the last word in the sheet

Sub test()
Dim fCell As Range
Dim rwBill As Long
Dim rwCopyright As Long
Dim lRow As Long

With Worksheets(1)
rwCopyright = 1
lRow = .Cells(65536, 1).End(xlUp).Row

    With .Columns("A")
        
        Do While rwCopyright < lRow
        
        Set fCell = .Find("BILL", after:=.Cells(rwCopyright, 1), LookIn:=xlValues, lookat:=xlWhole)
    
        rwBill = fCell.Row
        
        Set fCell = .Find("COPYRIGHT", after:=.Cells(rwBill, 1), LookIn:=xlValues, lookat:=xlWhole)
        
        rwCopyright = fCell.Row
        
        MsgBox "Data range to be used is: A" & rwBill & ":H" & rwCopyright
        
        Loop
        
    End With
End With


End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question