Copy data base on matching criteria (EXcel 2003)

Topic

New Reply

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

Reply | Quote

Replies

Not sure exactly what you want. I hope this will get you started.

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

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

Reply | Quote

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

Reply | Quote

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:
C:processABC 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Environ(“Username”)

Reply | Quote

Something like this perhaps?
Steve

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

Reply | Quote

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

Reply | Quote