Improving existing macros

**davidm_uk** · 05-18-2014, 10:35 AM

I've produced a few macros now, by a combination of using recording and help from here, which all work ok, but I want to improve their "efficiency" by reducing the amount of activating and selecting during copying between workbooks. I've put together a simple macro just to try and work out the correct syntax, but am struggling to get it right.

The example has 3 workbooks, code.xls containing the macro, booka.xls containing some source data, and bookb.xls is the workbook to which I want to copy data, all in the same folder:

Please Login or Register  to view this content.

This works ok.

However I want to get to the point where the macro is in booka.xls, and both booka.xls and bookb.xls are already open, with the correct sheets selected before the macro is run, but I can't get the right syntax for this. Obviously the Set wkbkFrom and Set wkbkTo lines fail because the workbooks are already open. I also think it should also be possible to lose the .Copy in the last two lines and use an =

It's the workbook and worksheet events (I think) that I'm struggling to understand their use.

If someone could give the correct coding, and maybe explain how it's actually working, I might start to understand it a bit more.

**RudiS** · 05-18-2014, 10:56 AM

Hi David,

See the code below...
I have modified it to assume that the code exists in BookA so that you can eliminate the workbook called Code.xls
There is no need to ensure that the correct sheets are selected as the code has exact references to the needed sheets.
Also, the copy option is better that the "=" options as any formatting will be transferred by the copy (but NOT with the = option)

Please Login or Register  to view this content.

**Miroslav R.** · 05-18-2014, 10:58 AM

Hi there
If the workbooks are open,do not use fullpath,just only wnk name.Then You do not need the wbk.open rows.I rather use wbk name as string and then use it like 'workbooks(mywbkstring)...'.
The fullpath use only in case the workbooks are not open.

send from my xperia z via tapatalk

**davidm_uk** · 05-20-2014, 01:44 PM

Thanks RudiS, works ok when I've changed the wksTo and wksFrom references, which you'd got mixed up a little.

It doesn't work however when I change the Range definitions to Cells(x,y) which was the next step in trying to understand all this.

e.g.

wksFrom.Range(Cells(a, b), Cells(a, c)).Copy wksTo.Range(Cells(x, y))

i.e. not specifying the end Cells reference in the wskTo range, as you can when using the wksTo.Range("B2") method.

I'll need to rethink this.