Vba code working in workbook module but not working from personal.xlb

[satputenandkumar0]

Dear Members,

Below VBA code copies certain columns in to active workbook from file after selecting . Code works perfect there (In workbook module). If I kept this code in personal.xlb, It is giving Run-time error'9':Subscript out of range see red line where error comes.

Please Login or Register  to view this content.

[WasWodge]

ThisWorkbook refers to the workbook the macro is in i.e. the personal workbook.

Try using ActiveWorkbook.

[satputenandkumar0]

Dear WasWodge,

Thanks for reply.

same error after changing

[WasWodge]

Then try referring to the actual workbook name or wbSource.

[satputenandkumar0]

sir,

I use this code for many workbooks.

[WasWodge]

See the edit = wbSource

[Norie]

Do you need the line causing the error?

[satputenandkumar0]

Yes sir,

I want line for activate current workbook

[JBeaucaire]

I believe this is correct. Note, when developing a macro that will reside in your Personal file, ALL commands referencing sheet ranges must be attached to a parent sheet object to properly target the command. The best way to do this is to declare worksheet and workbook reference at the beginning of the macro for all objects you will address, that includes the both the SOURCE objects and the DESTINATION targets.

Towards the end of your macro you are calling another macro. I believe that macro should be amended to accept an object reference fed into from this macro so the other macro has the address of the object HTC already as well.

Please Login or Register  to view this content.

[satputenandkumar0]

Dear JBeaucaire,

sorry for late reply. I was out of town.

Your solution works perfect. And very thanks for your valuable guidance.