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?

 

[Andrzejek]

Your link does not work, so it is hard to see what you are trying to do.

Is your text file something like:

Bill
abc
xyz
copyright
Bill
def
ijk
copyright

And you want it to be in Excel:

Sheet1:
Bill
abc
xyz
copyright

Sheet2:
Bill
def
ijk
copyright

???

Have fun.

---- Andy

 

[SkipVought]

Hi,

Why are you importing this into Excel? There is almost no added value to having this in Excel in this REPORT format.

Open it in a text editor and copy & paste at will.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lukn4it]

Can anyone help tell me if it is possible to do what I am asking to do?

This is part of a lot of filtered data, and this is all I have left in developing a new process for our accounting dept.

It seems like a very basic operation to develop, maybe I am wrong, maybe it cant be done.

 

[xlbo]

Of course it can be done. What have you tried? where are you stuck? what problems are you having doing this?

Try the macro recorder to get some basic code..

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

 

[SkipVought]

"... new process for our accounting dept."

Spreadsheets are designed for tabular data. That is not the kind of data in your example. A spreadsheet is not a good tool for that kind of format. Why not just open in Notepad and copy and paste into a new notepad and save as pdf1 etc.

The more important issue to me would be getting the data into tabular format, so that you could employ the plethora of features in Excel that work so well with TABLES.

Where is the SOURCE of the pdf's that were generated?


Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lukn4it]

I can easily put the data in another format, but it still seems the same type of formula will be needed. I still will need to search and find the "Bill" and cut it then paste into another sheet. Please dont try to determine its use, it is a piece of a lot of other data which is why it must be used in Excel. The reason I dont just cut and paste it manually is because there are alot more invoices than just the three you see in my sample. It is very time consuming and once the invoice is pasted into the new sheet I have a macro that searches and calculates the invoice from there. It gets very complicated to give you all the information on the entire project. I found it might be easier just to tell you the issue at hand. If I can find a way to cut this data and paste it into the other sheet, I am done with the project and will have saved our company two days of work, that will now be only seconds and capable of being handled fully by only one employee.

 

[SkipVought]

This is akin to using a hammer to drive in a screw.

Turn on your macro recorder and record finding the text in the last row of each invoice. Post back with your code.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[lukn4it]

Ok, this might help. I get the invoice from the internet, the only way I can pull the information is by using Adobe PDF - which is why it is in text format. I take the pdf and convert it to text. If I change the format now, I will have to start the entire project over. I was hoping to work with what I have. I know it is not a easy request, due to the format, but I didnt know if it could be done. I am being told that I might have to write a program with pearl instead, in which I would have to take hours to learn.

I cannot record a macro and send it to you because the information is not consistent in each invoice. Which is why I wanted to use a search and find feature of some sort. Basically, is it possible to look at that sample and tell yourself "I want to automatically cut and paste each of these invoices to another sheet" "I have to search for a way to cut each invoice knowing that they are not all consistent except that they all start with the word "Bill" and end with "copyright".

 

[xlbo]

You CAN record a macro

You can record yourself using the FIND function in excel to search for the text BILL. You can record yourself then searching from there for COPYRIGHT

You can record yourself cutting and pasting the data to another sheet

Once you have the basics, we can help you modify it so that it will loop through all your data in 1 pass.

Please bear in mind that we are here to help you learn, not to just provide you with code you don't understand. Search in VBA help for the FIND / FINDNEXT methods. Read up on the properties of a RANGE (especially the ROW & COLUMN properties), look into LOOPS and the CUT / PASTE methods. Record a macro that finds ONE piece of data, pastes it elsewhere and think about what you might need to do with that to make it cut a RANGE of data and paste it elsewhere...

Basically, most people here are more than happy to help with someone who has tried to do something and is stuck. What we generally won't do is help someone who is not willing to put some effort into learning themselves...

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

 

[lukn4it]

Now I feel a bit insulted, maybe this was too much to ask. I dont recall even asking for code, I asked if it were possible to do and how. Maybe you did not read the prior posts. I dont like asking for help, I like figuring things out on my own, specifically for this reason. I did look into find and using the macro resolve the issue. I tried recording my steps and considered the long step by step process. I would do it if it worked. I familiar with the range and difference for the codes row and columb. I even found information on looping. The problem is that it did not work. I dont know how to send you code to use as a sample if it has nothing to do with what I need. I can make some off the wall macro recording, but it will serve no purpose with this project. I am now looking into using Perl, because I am at the assumption that no one knows how to do this.

 

[SkipVought]

"I asked if it were possible to do and how."

Yes, it can be done.

Macro record doing as may pieces of the process as you can manually perform.

Put the macro pieces together and customize.

This is often the process that I use in building complex procedures.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[xlbo]

Don't feel insulted. I did read the prior posts - you have consistently not provided any sample code that showed what you have tried and where you were stuck. You have continued to re-iterate your question of "can this be done" without specifying what exactly you need help with

I said in a prior post that it could be done - it should be pretty simple. I also asked you to post back with what exactly you were stuck on and suggested using the macro recorder to get some code. You have not responded to that post and went on to say that you couldn't use the macro recorder....

I have given you keywords to lookup and asked you to post back your recorded code so that we can help you adjust it to make it dynamic

All we have asked for since the start of this thread is for you to post the code you are having trouble with. I don;t know why you think the code you have is not relevant - it shows part of your process - of course its relevant....

If you are still interested in getting a VBA solution, please post your code and you will see how much help you get...

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

 

[PHV]

I am at the assumption that no one knows how to do this
So, who are really insulted ?

 

[lukn4it]

Ok, this is how it works so far, I pull an invoice off the internet using adobe pdf (my only option as far as I know, there is no option on the website). I then convert the pdf to text format and import it into excel.

I have to pull specific information from each invoice in which I did in the code below:
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C1:R95C8,MATCH(""FPL"",R6C1:R95C1,),MATCH(4,R6C1:R6C8,))"
Range("K8").Select
Selection.copy
Range("K9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C2:R59C4,MATCH(""new"",R6C2:R59C2,),MATCH(4,R6C2:R6C4,))"
Range("K9").Select
Selection.copy
Range("K10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C4:R58C5,MATCH(""owe"",R6C4:R58C5,),MATCH(5,R6C4:R6C5,))"
Range("K10").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C4:R58C5,MATCH(""owe"",R6C4:R58C4,),MATCH(5,R6C4:R6C5,))"
Range("K10").Select
Selection.copy
Range("K11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C4:R58C5,MATCH(""charges"",R6C4:R58C4,),MATCH(5,R6C4:R6C5,))"
Range("K11").Select
Selection.copy
Range("K12").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("K11").Select
Selection.copy
Range("K13").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C1:R81C3,MATCH(""kWh/day"",R6C1:R81C1,),MATCH(3,R6C1:R6C3,))"
Range("K13").Select
Selection.copy
Range("K14").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C1:R82C5,MATCH(""kWh"",R6C1:R82C1,),MATCH(5,R6C1:R6C5,))"
Range("K14").Select
Selection.copy
Range("K15").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R64C1:R76C5,MATCH(""kWh"",R64C1:R76C1,),MATCH(""may"",R64C1:R64C5,))"
Range("K15").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R64C1:R76C5,MATCH(""kWh"",R64C1:R76C1,),MATCH(""may"",R64C1:R64C5,))"
Range("K15").Select
Selection.copy
Range("K16").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R64C1:R76C5,MATCH(""Off-peak"",R64C1:R76C1,),MATCH(""require"",R64C1:R64C5,))"
Range("K16").Select
Selection.copy
Range("K17").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R64C3:R78C4,MATCH(""reading"",R64C3:R78C3,),MATCH(""require"",R64C3:R64C4,))"
Range("K17").Select
Selection.copy
Range("K18").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C1:R75C3,MATCH(""maximum"",R6C1:R75C1,),MATCH(3,R6C1:R6C3,))"
Range("K18").Select
Selection.copy
Range("K19").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C1:R78C2,MATCH(""kWh/day"",R6C1:R78C1,),MATCH(2,R6C1:R6C2,))"
Range("K19").Select
Selection.copy
Range("K20").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C1:R80C4,MATCH(""kWh"",R6C1:R80C1,),MATCH(4,R6C1:R6C4,))"
Range("K20").Select
Selection.copy
Range("K21").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C2:R18C3,MATCH(""Dates:"",R6C2:R18C2,),MATCH(3,R6C2:R6C3,))"
Range("K21").Select
Selection.NumberFormat = "@"
Range("K21").Select
ActiveCell.FormulaR1C1 = _
"=INDEX($B$6:$C$18,MATCH(""Dates:"",$B$6:$B$18,),MATCH(3,$B$6:$C$6,))"
Range("K21").Select
ActiveCell.FormulaR1C1 = _
"=INDEX($B$6:$C$18,MATCH(""Dates:"",$B$6:$B$18,),MATCH(3,$B$6:$C$6,))"
Range("K21").Select
ActiveCell.FormulaR1C1 = _
"=index($b$6:$c$18,match(""Dates:"",$b$6:$b$18,),match(3$b$6:$c$6,))"
Range("K21").Select
Selection.NumberFormat = "General"
Range("K21").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C2:R18C3,MATCH(""Dates:"",R6C2:R18C2,),MATCH(3,R6C2:R6C3,))"
Range("K21").Select
Selection.NumberFormat = "m/d/yyyy"
Range("K23").Select
Selection.NumberFormat = "m/d/yyyy"
Range("K21").Select
Selection.copy
Range("K23").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C2:R18C5,MATCH(""Dates:"",R6C2:R18C2,),MATCH(3,R6C2:R6C5,))"
Range("K23").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R6C2:R18C5,MATCH(""Dates:"",R6C2:R18C2,),MATCH(5,R6C2:R6C5,))"
Range("K24").Select
End Sub

The problem is, it only works per invoice and I have 60 new invoices on a daily basis. The reason the macro was not set up for the seperation of the invoices is because the information is not lined up consistantly, each invoice varies. The macro will not work for information that is not consistant. I can send a copy of the macro for copy and paste of bill to copyright but this will not work for every invoice so it is not useful. I am very new to this and have never requested information or help through a forum, although I do read them. It is not easy to explain what is needed, this is a learning process. I guess I expected a simple answer, and assumed you could see and understand what I am looking for. I did not at any point want the code written for me, I simply needed to know, yes it can be done and through just using a macro or no I need to look elsewhere. I dont need a lot detail or code. If anyone felt my comment about assuming no one knows how to do this, then I appologize sincerely. Seems very touchy feely in here... I do appreciate your help if any, but I request that you are patient and understanding that we are not all good at explanation. Thanks

 

[lukn4it]

This is a macro of the cut and paste, but does not include the search and find to locate the beginning and end of each invoice.

Range("A3:H77").Select
Selection.Cut
Sheets("PDF 1").Select
Range("A7").Select
ActiveSheet.Paste
Application.Run "test.xls!PBA"
End Sub

 

[Gavona]

but does not include the search and find to locate the beginning and end of each invoice

This is easily solved. Switch on the recorder and...
Select cell A1
Edit, Find "Bill"
Edit, Find "Copyright"
Post the code

Warning; it would appear that the word Bill can appear in places other than the start of the Bill "understand your Bill" etc.

I wonder if it would help to insert a helper column A to the left of the current data. Use a formula in A2 such as:
=if(left(B2,4)="Bill",A1+1,A1)

Copy this down your workbook. It should define each Bill for you so you need to cut/copy all the rows until the value in column A changes.

Gavin

 

[Gavona]

Actually you could put a formula in cell A1:
=if(left(B2,4)="Bill","x","y")
Then highlight column A
Edit,Copy
Edit,PasteSpecial,Values

Edit,Replace x with nothing

now you can use

Edit,Goto,Special,CurrentRegion

to select an invoice

copy to your new sheet

Ctrl-Down to find the next invoice

Gavin

 

[Gavona]

And post the recorded code so that folk can help you to fine-tune it

Gavin

 

[lukn4it]

I need a bit more explanation, help me understand where this is going. Cant I just search columb A and when Bill and copyright are found it will return the range from bill to copyright A:H. The Bill cell and Copyright cell although is located in Columb A specifically although Bill is repeated through out the invoice, is there a way to just search columb A? I will send the code for what you sent me, but I just dont see the whole picture yet.