Excel Macro Filter/Move Data To Existing Wsh from a VBA noob

Topic

New Reply

I have been searching and searching for a macro to move data into an existing worksheet and now humbly bow to the Guru Team.

My workbook consists of sheets named eMailThis, Results, Banks, Landlords and seven sheets (CopyHere_1 – CopyHere_7 each with 13 columns). From column 5 and 13 it has to filter and remove rows to the sheets named Landlord and Bank based on text values of (from column 5) rnt or RNT to Landlords; and from column 13 values of BANK, TRUST, MORTGAGE, LLC, LOANS, LOAN, MAE, INVESTMENT and/or ESTATE to the Bank sheet. The next step is have the remaining data rows in the seven CopyHere sheets move to the Results and the data from the Banks sheet be at the end of the data copied from the CopyHere sheets, the eMailThis sheet uses formulas (Proper, Right, Left and Copy) to sort the data from the Results page to send into Publisher as a data source to populate emails.

Thus far all the macros I’ve attempted to use/alter create a new sheet “Summary/Master’ and this results in #REF errors on the eMailThis sheet, (and with my extremely limited VBA knowledge,) a huge headache! I’ve done the recording but Oiy! that was a mess to look at…

From an extremely grateful fan of my saviours! (btw, Your answers will fill my Christmas sock this year.)

Reply | Quote

Replies

Could you attach a sample workbook and detail the exaxct steps you want the macro to do? Perhaps show us a start and a finish workbook…

Steve

Reply | Quote

Hi elf named Steve, Here’s a book I’ve been working on this morning. Thanks for looking at it! It’s a work in progress and I hope it will give you the information you need! First time uploading a file to post, fingers crossed it works on this attempt.

29586-New_Listings_1-SampleBook-InProgress

Reply | Quote

I am not sure what exactly you want looked at and what you want done. I don’t get a sheet named “Summary / master” from your code.The errors in your formulas on “emailthis” are from blank cells in results (so there is no “:” to find). Do you want a formula to fix that error or what exactly are you asking for help with. Please be specific about what you want help with…Steve

Reply | Quote

Hi Steve, Thanks for taking an interest in this situation. All the samples I’ve tried to this point were removed from the Modules. What I’ve been trying to do is after data has been added to the CopyHere sheets is use macros to filter then move the rows to other sheets in the book.

The filter aspect is moving rows which have certain text values in columns 5 and 13, the most common are rnt or RNT in column 5, (the rows with this value have to go to the Landlord sheet.) From column 13 when the values of BANK, TRUST, MORTGAGE, LLC, LOANS, LOAN, MAE, INVESTMENT and/or ESTATE, the entire row has to go to the Bank sheet. Then the remaining rows in the CopyHere sheets have to copy to the Results sheet afterwhich the rows from the Bank sheet follow at the bottom of the rows copied to the Results sheet.

To this point each macro I have tried using will create a new page, and I have always seen #REF errors on the eMailThis sheet after the macros is finished. So I’ve been looking/trying to create a macro to move the information to (from what I can determine are destination sheets,) which are already created within the workbook and not create new sheets.

I could be misunderstanding how the operations work and might be looking for something more complicated, inasmuch as if a macro can only create a new sheet, then will it also write in the formulas for that newly created sheet to not have the #REF errors?

Again, thanks for your interest, it is heartening to see someone being involved with “my problem!”

Reply | Quote

If you don’t want a new sheet created, you don’t have to do it, you can copy or move records from one sheet to any of the sheets.I still am not sure exactly what you want done. If I start with the sample workbook you have, copyhere1 and 2 have items in it. 3-7 have only header info. what info do you want copied from these sheets and where do you want it to go? Is there is reason for 7 copies?Again if I start with your current sample workbook, what do you want it to look like after the macro runs?I can’t help with your problem if you don’t explain to me what that problem is…..If you are going to go from the start to the end workbook, what steps would you take (manually) to make the changes?Steve

Reply | Quote

Gosh! I knew what is in my head might be hard to explain so I really hope this removes any frustrations my issue could be causing. The fact you keep asking me for more information is reassuring! So here goes what I do to complete the workbook.

Here’s the steps I go through:
1. Go to webpage and enter parameters for search. I’m able to receive back upto 250 results from database.
2. I download the results in csv format and copy into CopyHere_1, then sort the page based on column 9 to match the webpage.
3. Click link of first result returned in webpage to open another page of details to copy an item for column 12.
4. Click another link in the details webpage to open an item returned from a different database to copy the information for column 13. Close that page and click for next detail page to repeat those two steps of one bit from first page then click link to get second bit from the page opened from other database.
5. Copy the information for 12 and click link for information for 13 and click next page link again, and again and again, all the while adding to columns 12 and 13.
6. After all the information has been added to 12 and 13 I ask the first database to return second set of information and copy returns to the second sheet named CopyHere_2. I continue to collect the relevant information for the 12th and 13th columns.
7. Repeat the process untill all the seven CopyHere sheets are completed.
8. Sort each CopyHere sheets based on column 5 and remove the relevant rows to the Landlord sheet, value rows to remove are ‘rnt’.
9. Go through all the CopyHere sheets to remove rows based on values in column 13 (BANK, TRUST, MORTGAGE, LLC, LOANS, LOAN, MAE, INVESTMENT and/or ESTATE) and move those rows to the Bank sheet.
10. Copy all the remaining rows from the CopyHere sheets to the Results sheet.
11. Copy the rows from the Bank sheet to add to the bottom of the eMailThis sheet.
12. Save workbook and move it to another computer to use as data source for a letter which is mailed out to respective party based on information from the eMailThis sheet, (using column 3) through Publisher.

*Using formula on eMailThis sheet to remove mailto: link of information in column 12 of CopyHere sheets and formula to reverse the information collected into column 13 of CopyHere sheets which is added to the body of the emails sent.

**Only Publisher mail merge adds to the subject line of email using data source fields, columns 1, 4 and 5 of eMailThis sheet.**

This entire process can upto ten hours of work depending on the results returned from the seven searches of the first database. If this could be done with a web query, life would be a breeze!! But that’s a whole other issue.

Could I send you a coupon for a free bottle of aspirin?

Reply | Quote

1-7) are you looking for help with extracting the information from the webpages (Steps 1-7). I have no experience with that so can not help…

My understanding of other steps:

8) you want to moves the rows on CopyHere 1-7 from their sheets to the the landlord sheet when value in col E = “rnt” (I presume not case sensitive)

9) Move rows from copyhere1-7 whose rows have any of the items (BANK, TRUST, MORTGAGE, LLC, LOANS, LOAN, MAE, INVESTMENT and/or ESTATE) in Col 13

10) copy (or do you mean move?) any other items from copyhere1-7 to results

11) Copy the items in bank sheet to the bottom of email this [You already have over 700 rows of formulas on this sheet, I don’t understand why you would want to do this, should it remove the formulas first? or what?]

12) the macro can save the workbook, but it can not move the file to another computer…

Your formulas in email this result in lots of errors due to blank cells, is that really what you want? If not what do you want??

Steve

Reply | Quote

Looks like you are getting where I am, Steve. I realize I have to ‘get’ the information from the webpages, yes.

The move based on value in 5th (E) column from the CopyHere 1-7 sheets to Landlords, yes. Case sensitive? It copies from the csv download in both upper and lowercase formats.
The move based on values in 13th (M) column from the CopyHere 1-7 sheets to Banks, yes. This column is always in uppercase, hence the Proper formula on the eMailThis sheet.
The move (or copy, doesn’t matter) of the remaining rows of the CopyHere 1-7 sheets to the Results page, yes.
The move (or copy, doesn’t matter) of the Banks rows to the bottom of the eMailThis sheet, yes.
Save the workbook as long as it asks me what to save it as, is a bonus.
The #VALUE errors I know are there because I never know how many rows will eventually be in the Results page and I always start from the same workbook and Save As right after I open the “template” workbook. So removing the formulas before the rows from the Banks sheet is okay, (I assumed the formulas would be removed when rows are moved or copied onto/into the rows. You know better than I!)
Moving it to another computer I can handle on my own, thanks. (o:

I’ll have a word with Santa about your Christmas wish list. Seeing as you are taking care of mine!

Reply | Quote

In email this sheet the headers do not match the bank data, do you want the bank headers added as well?

The formulas can be removed first (but the headers in the sheet will still not match the bank data rows).

If the formulas are removed from email this and then the rows from the bank sheet are added, are the formulas supposed to be readded? [They can be added as far as the rows in]

Do you want the data moved or copied. If moved, it will no longer be in the CopyHere sheets, if copied it will be in both locations.

Another confusion: do you want the move/copy to overwrite the existing data or be appended to the data?You need to be specific about what you want…

Steve

Reply | Quote

I see the conundrum of the headers not matching from Bank to eMailThis sheets as you describe.

As you’ve seen the formulas for the last two columns in the eMailThis page take the first word (or last name) from column 13 and extract it to its own column in the eMailThis sheet. That is not supposed to happen when the bank info is passed to the EmailThis sheet. So IF column 13 (M) of Bank sheet remains unchanged when the rows are moved/copied to the eMailThis sheet, yet the rest of the formulas in (eMailThis – columns 1,2,3,4, and 5) could be in place to get the resorted data from the Bank sheet, you can be/are considered a VBA God..!

Conversely, if when the rows are removed from the CopyHere sheets into the Bank sheet and the last word of column 13 were placed first, then the formulas of the eMailThis sheet would continue to work by switching the first word back into its own column and the bank name would appear as it started out in the CopyHere sheets. Does this convolution make any sense? Sort of a double reversal?

As for the move versus copy item I think having it in both sheets (CopyHere and eMailThis) would be fine, Steve.

As to the last question/comment I’m not sure to which area you are referring to unless it is the bank data again, and for that, what matters is the first name of bank data column 13 be the value of the eMailThis column 6 (F).

Your patience and understanding in this make you next to godliness in my workbook, anyway!! Big bow and thanks headed at you Steve!!

Reply | Quote

I see the conundrum of the headers not matching from Bank to eMailThis sheets as you describe.

Then why copy the bank data to email this?

As you’ve seen the formulas for the last two columns in the eMailThis page take the first word (or last name) from column 13 and extract it to its own column in the eMailThis sheet.

But you have asked to put all the columns of the Bank sheet into the email sheet and after row 700, leaving many “formulas with errors” to wade through to get to it.

That is not supposed to happen when the bank info is passed to the EmailThis sheet. So IF column 13 (M) of Bank sheet remains unchanged when the rows are moved/copied to the eMailThis sheet, yet the rest of the formulas in (eMailThis – columns 1,2,3,4, and 5) could be in place to get the resorted data from the Bank sheet

The emailthis sheet formulas look at results NOT the bank sheet. The bank sheet data is not in results: ONLY the things not in banks or landlords sheets…

Conversely, if when the rows are removed from the CopyHere sheets into the Bank sheet and the last word of column 13 were placed first, then the formulas of the eMailThis sheet would continue to work by switching the first word back into its own column and the bank name would appear as it started out in the CopyHere sheets. Does this convolution make any sense?

Not really, Since the emailthis sheet formulas does NOT look at the bank sheet, it looks at the results sheet, which contains items NOT in landlords or Banks…

As for the move versus copy item I think having it in both sheets (CopyHere and eMailThis) would be fine

You haven’t asked for anything to be copied into “email this”. A complete listing would be in CopyHere, a subset would be put into landlords, some into banks, and those not in landlords or banks would go into results. emailthis would continue to refer to results and thus ignore anything dealing with landlords or banks…

As to the last question/comment I’m not sure to which area you are referring to unless it is the bank data again, and for that, what matters is the first name of bank data column 13 be the value of the eMailThis column 6 (F).

It referes to items copied/moved from the copyheres to either the landlord, bank, or results sheets. If there are items in the sheets at the start of the macro on those sheets are they to all be erased, should the new items be appended to the end of any previous data, should the data overwrite data (and how should the overwriting be determined?)

Could you take the existing sample file you uploaded as a “before”. Manually do what you would like to it and upload the “after” so I can see the final results of the macro. Perhaps this will explain what you want done. (it would also be good if you could describe the steps you take in some detail). As you should be able to see from what I asked above, it does not seem to me that I understand what you want since your comments seem to me to be inconsistent in what I see the macro doing…

Steve

Reply | Quote

Hi Steve,
I’ll do each statement each in it’s own reply…
The Bank data has to be mailed out and the data source for Publisher is the eMailThis sheet, therefore the bank data is included. The problem has always been the names get reversed, so in my wish list is having the names transfer from the CopyHere sheets correctly to the eMailThis sheet.

Reply | Quote

3. Oops! You’re right on that, it should have been CopyHere and Results sheets. Sorry (o:

Reply | Quote

2. The data is not put at the end of the formulas, I put the Bank rows at the end of the data appearing on the eMailThis sheet as copied rows and the formulas are removed from the cells when the info is copied in. Then I highlight the remaining rows and Clear Content, then save the file again.

Reply | Quote

4. Each time I start with the same “template workbook” with the sheets named, the column headers in place and the formulas on the eMailThis sheet. After I open the book, I Save As so each book is named New_Lisings_(today’s date) in case I need to reference the information at a later time.

Up to this point I always copy the two columns of the Bank names to a new sheet and copy them back to the eMailThis sheet in the opposite order so they appear correctly and not with the first word last. Then I always delete the sheet and save the book again.

Reply | Quote

Hi Steve, From your notes this morning I begin to see your issue with my steps so I went to a ‘template book’ and made a couple alterations… by adding a Banks2 sheet with the formulas from the eMailThis sheet which reference the Bank sheet, the information then appears the same for the eMailThis sheet (almost. The bank or company names are incorrect.)

To get the bank info I:
Go to each CopyHere sheet and copy row with bank or company name to Banks sheet.
Go back to each CopyHere sheet and clear contents of row with bank or company name.
Run the DeleteRowsWhereColumnMIsBlank macro.
Copy the remaining rows from the CopyHere sheets to the Results page.
Paste Special (Values) the cells 1-5 from the Banks2 sheet to the bottom of the eMailThis sheet.
Then Paste Special (Values) the cells from Banks2 column 6 to column 7 in the eMailThis sheet, and
Paste Special (Values) cells from column 7 of Banks2 to column 6 of eMailThis sheet.
Clear Contents of rows with formulas in ’empty’ rows in eMailThis sheet.

Please see the attached workbook for a ‘finished book’. (Information contained within the book is sample info and is incorrect but the results are there to see.) 29595-New_Listings_12-8-11-test1-results

I’m going to open up a bottle of aspirin on your behalf and partake a pill or two!!

Reply | Quote

You seem to have changed things around again. I presume that this “result” sheet is not the output from the original file you posted. I will try to look at it when I get some time. Unfortunately I had more time to play with it this weekend and have less time now that I have my job work to do…

Steve

Reply | Quote

Dear Dr, Wow!

I completely understand and again Thank You for the time and effort you are exercising on my behalf. Given your area of work, I have a better understanding of any frustrations my changes and lack of communicating my direction to you. I’ve left a couple private messages to you if you care to check.

I look forward to our future communications, and now really, really like the avatar of yourself!! (I have to find something of me pulling my hair out.)

btw, nevermind about the coupon for the free bottle of aspirin, I know you can make your own!

Reply | Quote

Would you mind uploading the book you have been working on? If I could have a ‘crack’ at it whilst you are overwise occupied, it would be greatly appreciated!

Reply | Quote

I am working on the 2 you have uploaded trying to understand what you start with and end with…

Steve

Reply | Quote

I had a chance to look at your 2 files and re-read this thread. I admit to a little confusion, but believe I have a slightly better understanding.

Some points and comments. Could you please verify each point and correct any inaccurate presumptions and conclusion:
1) I cannot see how the first file can be made into the 2nd file. I have therefore concluded they have nothing to do with one another and are just 2 example workbooks.

I will concentrate on the more recent 2nd file since it seems more “complete” as the “final”. Some comments/questions:
2) Do you really need the results sheet and the Banks2 sheet? They seem to be intermediate sheets and could be eliminated if you did not use formulas in email this…
3) Is there a a reason that the data from copyhere7 is NOT in the email this sheet? Is it just an oversight?
4) Is there a required order to the data on emailthis? It currently is CopyHere6, CopyHere1, CopyHere2, CopyHere3, CopyHere4, CopyHere5, and then the info from Banks. Is there a reason for this order or is the order immaterial?
5) The Bank Data is not in any of the CopyHere sheets. I presume this is meant tio represent that they were MOVED from one of the copyhere sheets to the Bank sheet.
6) The landlords sheet has no items. I presume in this example that there was NO landlord items in any of the copyhere sheets.
7) if I recall correctly the email this sheet does NOT get any of the landlord data.

I have made this request before an I make it again. It would be helpful if you could attach 2 files. The first should be a representative file of the file that the macro will START with. Sheets should be be blank if they will always start blank, and if they have some values add some (3-5 should be sufficient) representative items with data in every cell that would have data (even things like “First1 Last1” as a full name and “Address1” for an address). It should have things for the landlord page, and use various forms of bank names. If you want to append data to landlords and banks from previous runs (and even email this) you should have some examples in the file as well.

The 2nd file should be what you NEED to have at the end based on the representative starting file. If you need formulas and intermediate sheets to create the film, but don’t need it at the end, copy and paste-values the formulas and get rid of the intermediate sheets. You can geet rid of anything in the 2nd file that you don’t really need. If you only need the emailthis, the banks and the landlords sheets (and no longer have need for the copyhere sheets, then don’t include them.

If you need data sorted make sure you try and indicate this and any other details to help understand the logic of going from the 1st to 2nd file. Before I will start writing any code, I need to understand what the code will be doing and at this point I only have an idea (though I have mapped out in my mind how to do those things).

Steve

Reply | Quote

Hi Steve, Apologies for the lengthy delay. Your questions and all the workbooks piling up had me realize trying to do all this with Excel is a piece of the pie. When I started to look at the whole pie, I realized Access would be a better place to be building a structure this complex, as the VBA for a single workbook required to filter only a portion of the data. It was during the creating the two files you spoke of when the epiphany hit.

So I Thank You for taking an interest in this and wish the best to you and yours over the holiday season. I am off to read more about creating databases now!

Reply | Quote

Good Luck. I’m sorry I couldn’t help more than I did. Unfortunately, I don’t use Access at all, so have no experience with it so I will leave it to other volunteers on that board.

Have a happy holiday season.

Steve

Reply | Quote