Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macro to filter and move rows into named sheets

    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.

    New_Listings_1-SampleBook-InProgress.xls

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  5. #5
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!"

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  7. #7
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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 [I presume put them at the end of the list if it has any items]

    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
    Last edited by sdckapr; 2011-12-10 at 17:30.

  9. #9
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Last edited by sdckapr; 2011-12-10 at 19:59.

  11. #11
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  13. #13
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  14. #14
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  15. #15
    New Lounger
    Join Date
    Dec 2011
    Location
    Calgary, Alberta, Canada
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    3. Oops! You're right on that, it should have been CopyHere and Results sheets. Sorry (o:

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •