Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy data base on matching criteria (EXcel 2003)

    Hi All,

    I have an active sheet with 10 columns of data of summary data

    Is it possible to have a macro which will go thru column C and copy the value in column F of the same row
    to another sheet with the same sheet name as in column C of the active sheet

    All existing sheets in WorkbookB are actually .xls templates and are already pre-named the same as column C
    of the active sheet. There are different sheet names in Workbook B and the macro need to find the correct matching
    sheet name with the name in column C of active sheet.

    TIA

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  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

    Re: Copy data base on matching criteria (EXcel 2003)

    Not sure exactly what you want. I hope this will get you started.
    <pre>Option Explicit
    Sub FrancizCode()
    Dim lRow As Long
    Dim rCell As Range
    lRow = 1
    Set rCell = Cells(lRow, 3)
    Do While rCell.Value <> ""
    With Worksheets(rCell.Value)
    .Cells(.Cells.Rows.Count, 1). _
    End(xlUp).Offset(1, 0).Value = _
    rCell.Offset(0, 3).Value
    End With
    lRow = lRow + 1
    Set rCell = Cells(lRow, 3)
    Loop
    Set rCell = Nothing
    End Sub</pre>


    It loops thru col C of the active sheet, until it finds a blank cell. It stores the value in Col F (3 cols to the right of col 3) in the first available row in col A of that sheet

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    Hi Steve,

    Thank for looking into this. I think I didn't explained clearly on this and I appologize for this.
    Let me explain one more time

    I have a worksheet which in col C are names and the names can
    be repetitive.eg ABC CR Fund A can be in C3, C12. But each row data
    are different.

    The folders prenamed in the first 3-5, eg ABC or STATE
    There many xls workbook templates named the same as the folders'
    namesIn these workbooks, there are many sheets named as the same as values
    in col C. Each unqiue name is in each sheet.


    I need a macro to

    1) loop thru col C until last row, get to the correct folder and
    open the workbook which reside in the folder. eg C:/process/ABC
    folder/ABC.xls workbook name

    2) using the value in col C, eg ABC CR Fund A is the value in col C2
    and find the matching sheet template's name eg ABC CR Fund A and
    copy some data to certain cells of the sheet template and save it
    for printing after the whole process. Then move one cell down and
    repeat the same

    The part which is daunting me is that if the next cell is having the
    same value as the above cell, and this can go on for the next few
    cells down,I need to copy/add the previously used sheet template and copy the
    certain data from the row to the new copied/added template

    In the sample xls workbook templates, the highlighted cells are
    variables according to the matching names in col C of the Utility
    workbook

    Hope this made sense.I appreaciate all assistance given.

    TIA for all help

    Regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  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

    Re: Copy data base on matching criteria (EXcel 2003)

    I am afraid I do not understand...

    Some of what I understand is to start in "Utility.xls" in C2 and get the info "ABC GR Fund I". The text up to the first space is "ABC" so it should open up the workbook:
    CrocessABC folderABC.xls

    In That ABC workbook should be a sheet named for C2 ("ABC GR Fund I"). I presume that you want some of the info from other columns in row 2 of the utility.xls to be put into that ABC sheet and then the sheet printed. Which columns and into which rows? Certain columns (which ones?) always into rows 12, 13, 17, 20, 41,42? or can they vary? the worksheet is then closed [does it need to be saved? it would appear not since it most likely will later be overwritten with a new set of data]

    Then in Utility one would look at C3 (ABC CR Fund A) again open up the same ABC.XLS only put info into cells of the sheet "ABC CR Fund A", print it and close it.

    etc etc?

    What should be done if the workbook does not exist and/or the sheet does not exist? Should that be checked before anysheet is printed or print only the ones that work and leave a message about the ones that do not work (or print a list, highlight them in Utility, or what).

    Please be very detailed about what you want done. Step us through what the code should be doing at each step...
    Steve

  5. #5
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    Hi Steve

    Thank for looking into this. I reproduced your questions here.

    1) Which columns and into which rows?
    In the Utility workbook, it will always the following following columns' data to be copy to the template worksheets
    Col F to cell B20 in the template
    Col G to cell B12 in the template
    Col C to cell B13 in the template
    Col O to cell B17 in the template
    Col J to cell B41 in the template
    Col A to cell B42 in the template

    2) the worksheet is then closed [does it need to be saved?
    Yes, I need to keep it for printing later

    3) it would appear not since it most likely will later be overwritten with a new set of data.
    Then in Utility one would look at C3 (ABC CR Fund A) again open up the same ABC.XLS only put info
    into cells of the sheet "ABC CR Fund A", print it and close it.

    Is there a possibility to create a template naming "ABC CR Fund A-1" by copying the format of the "ABC CR Fund A" and
    copy the next row's data in Utility to the same cell's area of the newly created template if the next row value in Col C is the
    same as the previous row?

    4) What should be done if the workbook does not exist and/or the sheet does not exist? Should that be checked before anysheet is
    printed or print only the ones that work and leave a message about the ones that do not work (or print a list, highlight them in Utility, or what).

    The workbook and its sheets should exist. I didn't think about this at all but you does raise a great question.
    In case if it doesn't exist, highlighting them in the Utiltiy would be idea. Or what would you suggest?

    Lastly, I do apologize for my unclear requirements

    Many Thanks in advance for assisting in this.

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  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

    Re: Copy data base on matching criteria (EXcel 2003)

    How does the code know that the sheet that exists was not saved previously? If I run the code today and there are no duplicates there will be so many sheets that are saved. If I run them tomorrow, they will be overwritten since it will be the first time the code is seeing them....

    It seems to me that at the end of the code, you will have many identically formatted sheets saved and duplicate data (in database form) as well. Why not just have a "printout template" sheet in the utility.xls workbook. You could select the item to fill in the sheet and print it directly from the database. You could write a routine to fill in each item if desired and print it. I am not sure of the need for redundant datasets especially for this type.

    Using just one template at "runtime" (rather than saving dozens) allows for changes to the template which will take place even if printing past data without having to recreate dozens (if not hundreds) of sheets.

    If you want to recreate the sheets, I would make a new copy for each item "-1, -2, " etc, but this could get unwieldy with large numbers of rows. You could even hide the "original" unnumbered ones and only use them for templates.

    Actually the ABC.xls sheets could be done with one template and just have some lookup table for the infor in some of the items in B that match the "sheetname"

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    Hi Steve

    I thank you for looking into this and providing suggestions. I am lost as to which offer the most efficent.

    This is a critcial part of a program that I am currently working on. It start from filtering for certain criteria and separate the data that I need to process
    today and the next day. It then copy the data which need to process today to a new workbook and sub totals the "group" , the sub totals are copy to
    another template and use for requesting rates for each group. Once I have obtain the rate for each group, I use a Index and Match formula to put the rate
    to the correct group in col N which will give the amount in col O.


    I have many workbook templates like the ABC.xls, each workbook is named after the "group" such as ABC, MS...... The creation of many templates are due
    to the differences in those cells which are not highlighted, these cells have fixed variables.

    The recreated sheets would be delete after printing as I only need to keep the original.


    1) Why not just have a "printout template" sheet in the utility.xls workbook. You could select the item to fill in the sheet and print it directly from the database. You could write a routine to fill in each item if desired and print it.

    2) Using just one template at "runtime" (rather than saving dozens) allows for changes to the template which will take place even if printing past data without having to recreate dozens (if not hundreds) of sheets.

    3) Actually the ABC.xls sheets could be done with one template and just have some lookup table for the infor in some of the items in B that match the "sheetname"

    Would the many templates ( approximately 200 ) that I have can fixed into these, pls enlighten me.

    4) If you want to recreate the sheets, I would make a new copy for each item "-1, -2, " etc, but this could get unwieldy with large numbers of rows. You could even hide the "original" unnumbered ones and only use them for templates.

    This is what I am thinking as I thought that the many templates I have with fixed values in those cells that are not highlighted.
    From the sample that I have provided, you will notice that different name in the same "group" have different "fixed" details ( cells that are not highlighted )
    I am not sure how the info goes into these cells unless I need to build a database on it.

    Steve, I am open to your suggestions as I consider myself a novice in this.

    Thanks

    Regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  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

    Re: Copy data base on matching criteria (EXcel 2003)

    Based on the limited data, it seems to me that you may be able to reduce the number of templates by creating a table for the "fixed variables". The 3 ABC templates you provide are identical in format and headings in Col A, the yellow items are different in B and obtained from Utility, I don't see a reason why you need 3 templates, you could just create another sheet in utility which had the "Account" and lookup the "fixed" items to fill in for rows 7, 22, 26, 28, 29,31,34,37, and 38. If your other templates are similar to the ABCs, I suggest 1 template and get the "fixed items" from 1 table and the other rows (Yellow items) from the other table. Then you do not have to save all the filled in sheets, just print them directly from the database in utility.

    But you only provide info on the ABC templates, there is nothing on the STATE, MYC, CRTT, CR or KKT templates, but I presume they are similar to the ABC templates...

    So I see 3 sheets in utility. the one you have. One that looks up the Account name and gets the "fixed items" and the 3rd which is the template and extracts the items from the 2 sources based on some selection...

    Steve

  9. #9
    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

    Re: Copy data base on matching criteria (EXcel 2003)

    This is what I was suggesting.

    The worksheet database is what you had for Utility. The Database2 is what I extracted for the "fixed" items in ABC and I just created some for example for the other templates. The Template sheet matches the ABC sheet. The yellow items are pulled from Database based on the item number in I1 (the row is 1+item number since you start the data in row 2). Based on I1 you extract the value of the account from Col C in I2. From the value of Account name in I2 it looks up the matching row in database2

    Next to the extracted columns is the sheetname, the column and the row to extract. The sheetnames and cols are hard-valued, the rows are based on either I1 or I3. When you change the value in I1 a new set is extracted.

    The printall button just replaces the value in I1 from 1 to the last item number and prints it so you can print them all.

    In the database sheet I added a new column for a unique description and this can be used in the template sheet to select a particular item from the list. The list is linked to I1.

    Steve
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    Hi Steve,

    Thank you for your wonderful suggestion. I think this is fine with me. It reduce lots of system resources and is efficient

    The template format are the same for all...ABC, MYC, and so on.
    I will need to build Database2 and is there a way to pull these "fixed values" to Database2 from the existing close to approximately 200 templates
    in a folder?
    How do I pwd protect the sheet Template except for the "Printall" button, cell I1 and the Listbox. Will these formulas works if I pwd protect these cells given
    that the users are not familiar with Excel and I does not want the user accidentially change any cell's value, thus erasing the formula in it.

    Possible to have the Database items marked "Processed ddmmyy" in col Q, "userid" in col R upon printing. This is for audit trail purpose.

    I need to think how to have the Database entries goes to this workbook as currently the program actually create a new workbook and name it "Utility".
    Thereafter, I copy the necessary sheets "SI" and "OL" manually to the Utility. I believe I am able to code this.

    I really appreciate you effort and help in this.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    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

    Re: Copy data base on matching criteria (EXcel 2003)

    I just combined them together, deleted the other rows, then copy and transposed them to create the ones for ABC. The others I just filled in numbered items. A macro could be created to do this. It is just opening the files and going thru each template to copy the data.

    It could also be done with the combining and reading them directly

    To PWD protect the sheet, Unprotect cell I1 then protect the sheet. This will allow I1 to be changed but not the other things

    You can print whatever you want for an audit trail. The date is easy to get [TODAY() or NOW()] the userID can be obtained by having them fill in a cell or via code by asking, reading the Username from Tools - Options or reading the username from the "environment" all could give different answers. You could even add those items in the footer whenever that sheet is printed.

    I hope this helps. If you need help with specific questions about this, you will have to provide specific details on exactly what you want/need.

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    Hi Steve

    I was just back from my own country and I apologize for not responsing earlier as I didn't have access to a PC.

    Would you provide a macro to write the "fixed details" from the templates to create Database2?

    in addition, would appreciate that you can provide the code for reading the username from the "environment" and I prefer your idea of adding those items in the footer
    whenever that sheet is printed.

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    The following expression will return the user's login name:

    Environ("Username")

  14. #14
    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

    Re: Copy data base on matching criteria (EXcel 2003)

    Something like this perhaps?
    Steve

    <pre>Option Explicit
    Sub CreateDB2()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim wDB2 As Worksheet
    Dim vWkb, vHeader, vAddress
    Dim i As Integer, j As Integer
    Dim lRow As Long
    Dim sPath As String

    sPath = "C:MyPath" 'set as appropriate
    'List of files to open in path
    vWkb = Array("ABC.xls", "STATE.xls", "MYC.xls", _
    "CRTT.xls", "CR.xls", "KKT.xls")
    'Header names for "Database2"
    vHeader = Array("Account", "ACCOUNT TO BE DEBIT", _
    "ADDRESS OF BENEFICIARY'S BANK", "ADDRESS OF BENEFICIARY'S BANK(2)", _
    "ACCOUNT NUMBER OF BENEFICIARY", "BENEFICIARY NAME", _
    "OVERSEAS BANK'S CHARGES", "OVERSEAS BANK'S CHARGES(2)")
    'Addresses for grabbing cells
    vAddress = Array("B26", "B28", "B29", "B31", "B34", "B37", "B38")


    Set wDB2 = ThisWorkbook.Worksheets.Add
    For i = 0 To UBound(vHeader)
    wDB2.Cells(1, i + 1) = vHeader(i)
    Next
    lRow = 1
    For j = 0 To UBound(vWkb)
    Set wkb = Workbooks.Open(sPath & vWkb(j))
    For Each wks In wkb.Worksheets
    lRow = lRow + 1
    wDB2.Cells(lRow, 1) = wks.Name
    For i = 0 To UBound(vAddress)
    wDB2.Cells(lRow, i + 2) = wks.Range(vAddress(i))
    Next
    Next
    wkb.Close (False)
    Next
    Set wks = Nothing
    Set wkb = Nothing
    Set wDB2 = Nothing
    End Sub</pre>


  15. #15
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy data base on matching criteria (EXcel 2003)

    Hi Steve and Hans

    I appreciate the codes and the guidance given.

    Steve, I try to codes aqnd get back to you if there's any further questions on this which shouldn't be if I have explained myslf well.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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