Macro Problem

[jimbojames136]

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?

 

ta

James

[LosOjos]

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?

 

ta

James

 

Copy all the sheets you need from the other workbook in to your template workbook and hide them. Then you can copy from those sheets within the template workbook instead

[jimbojames136]

no this doesn't seem to work as it still references the other workbook within the formula?

[LosOjos]

no this doesn't seem to work as it still references the other workbook within the formula?

 

Then you need to edit the formula to reference the sheet in your template rather than the other workbook; if you maintain the same sheet names, you can do a find an replace to fix it like so:

 

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!

[jimbojames136]

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.

close

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.

 

thanks

James