Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    Two workbook having same format (templates)

    Hi All,

    I have 2 workbook having the same format master.xlsm getting references from test.xlsm. Some times test.xlsm format change like some one insert a row or delete the row. I need that test.xlsm format(template) auto adjust range or cells like if on A1 (cell) on master.xlsm content name (work) then adjust the full row of test.xlsm to content name (work) and so on on full test.xlsm workbook.


    Thanks
    hammeed

  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
    Not sure exactly what you are after - could you clarify?

    If both files are opened when the changes are made, the formula links will adjust to the changes, and remain pointing to the adjusted cells.

    If you do not want to keep them pointing to the same cells, you could consider using INDIRECT function...

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir steve,

    I have two workbook (templates) having same format sometimes one template changed because of inserting or deleting the rows.Is it possible that vba code lookup the first template.xls and adjust the second template1.xls based on match data or content names criteria.

    Sorry if i could not clarify it...

    Thanks
    hammeed

  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 still don't understand...

    Perhaps you could attach a sample file and explain what you do, what happens when you do it and what you want to happen....

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir Steve,

    i have attached the sample-tests.zip folder. In which you can see two .xlsm file one is master.xlsm file (which is original template or fix template), second file is of test.xlsm in which i want to change ( you can see the highlighted rows are not matched with master.xlsm row number and cell range. I want that vba code auto adjust the rows data and content name as it in is master.xlsm e.g Row no 6 content name and data of test.xlsm are not same as master.xlsm. I need auto adjustment of test.xlsm same like master.xlsm template.

    Thanks
    hammeed
    Attached Files Attached Files

  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 I understand what you want, you can do it with the line of code:

    Code:
    Workbooks("Master.xlsm").Worksheets("Sheet1").Cells.Copy _
        Workbooks("test.xlsm").Worksheets("Sheet1").Range("A1")
    Put I don't understand why you would want to have 2 identical working copies of the same information. It is easier to maintain 1 copy than maintain 2 copies...

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    farrukh (2011-08-28)

  8. #7
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir Steve,

    Actually the template i have sent to you as a sample is not very big the actually template master.xlsm and test.xlsm range is about N100.The purpose of two template is that the main template which is master.xlsm is head office use which is fixed because it refer other third party import export tool (scripts). The template which is test.xlsm is (daily report) coming from the field on daily basis (what they do enter content name or cells data as they wish as i shown in sample). I do on daily basis first adjust the test.xlsm template as like master.xlsm then paste test.xlsm template intto master.xlsm on daily basis and i have to do this for 15 fields (other templates).

    Thanks
    hammeed

Posting Permissions

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