Hi all, im trying to set a excel template for data extract from SIMS.
im having a problem because im copying formatting and formulas from another workbook while creating the macro to do this. Therefore every time I run the report it needs the other workbook open.
is there anyway of getting round this other than having to write every formula manually?
no this doesn't seem to work as it still references the other workbook within the formula?
As an example, we'll assume the original workbook was called "Book1.xlsx" and the sheet was called "Sheet1" - if you look at a formula referencing the original workbook, you'll see the references begin '[Book1.xlsx]!Sheet1'
To fix them, you simply remove the reference to the original workbook using find and replace, replacing the reference with a blank and making sure to match case and check within forumlas rather than values. In the example, this would look like so:
Note that the "Replace with" text box is completely blank - if you put a space in there you'll break the formulas.
Final note - make a backup before you try this, just in case!
also tried that, the macro still references the other workbook to copy over the information.
it might be that i have the whole process wrong.
i have designed my report in SIMS.
taken a copy of the blank reportdata file.
run SIMS report.
edit report using record macro feature (this is where i copy formulas and formatting from another workbook)
stop macro record.
open VB script editor, take copy of code, and close without saving report)
open copy of reportdata and past in VB code.
run report in sims again with the template pointing at your copy of reportdata.
this could be completely wrong, its my first attempt at running a report like this.
There are currently 1 users browsing this thread. (0 members and 1 guests)