Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    NOLA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbooks to Worksheets (Excel 2000 SR2)

    Help 4 the macro impaired? I have a 2 part problem: 1st: A Call Reporting Package puts out weekly reports in seperate workbooks having the same name each week (I can choose to write them to a date named folder or to the same folder each week). I want to "automatically" copy the 5 workbook reports to a single workbook fronted by a Summary worksheet. (Can I reference an external workbook in a macro?) Leading to problem number 2: When I set up the summary formulas and reference an external workbook they retain their reference when I replace the workbooks with the new weekly report (they have the same name each week). However, if the formulas reference worksheets in the same workbook (I have manually opened each workbook and moved it to the summary workbook) when I replace the worksheet with next weeks worksheet, it seems to loose it's reference? Is there any way to do this other than by cutting and pasting the data from the external workbook over the existing data in the worksheet? Can I do that with a macro? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbooks to Worksheets (Excel 2000 SR2)

    Yes, you can open and reference other workbooks in VBA. There are many examples in the Forum. Here is one, and This is another.

    I think that the only way to keep your references will be to paste the new data over the old data on the same sheet. When you delete a sheet, any references to that sheet get changed to #REF, and the old reference is lost.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    NOLA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbooks to Worksheets (Excel 2000 SR2)

    My own kludgy solution: On my C drive I created a WEEKLYSTATS folder having a copy of the 5 report workbooks and a summary workbook with 6 worksheets. On worksheets 2 thru 6 I created a fully qualified reference to the appropriate workbook thru the entire data range (enter + in A1 then click A1 in workbook1, drag thru all the data and hit enter, close the workbook and the reference is updated to C:weeklystatsworkbook1). Then on the first sheet setup all the necessary cell references and formulas for a weekly summary. Now I can save the summary workbook as Summarydate and run a macro that selects the data in all 6 worksheets, copies and paste special values to convert the external references to absolute data then email it off to those interested. Now, each week I can copy the weekly reports into the WEEKLYSTATS folder, select all the data in Summary and EDIT/LINKS to update the data and presto! Not fully automatic or pretty, but it's working for now.

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    NOLA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbooks to Worksheets (Excel 2000 SR2)

    Thank you very much. "This" looks like it might be helpful ("Here" is more complex than I need). You can see my "Kludge-around to see how I defeated the cursed "REF" error. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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