Copying a range from a Personal file

[Timmymac]

Hi

Not sure if my thinking is the right way to go about this so would appreciate some help.

What I need to do is put data that I get on a variery of excel speadsheets into a standard template. I can do this by the process of copying over column by column the appropriate detail from one workbook to the other.

what I'd like to do is replicate the column headings of the template onto the workbook with the original data so I'm looking at just one workbook all the while until I'm ready to transfer all the data in one go.

I realise I can just copy from the template to the active workbook, but what I had in mind was having the ability to hit a button to replicate it associated with a macro.

So the macro I'd want would involve starting from a cell in the active workbook, obtaining a named range from my personal workbook (which would be the template headings) and then pasting this back into the active cell of the active workbook.

First of all....is this a sensible approach? And if it is feasible...how would I get it to work (taking into consideration the Personal workbook may be hidden).

the only code i have at the moment is

Workbooks("PERSONAL.XLSB").Worksheets(1).Range("MODELTEMPLATE").Copy

But how would I get this to paste onto the cell I start with?

[Bernie Deitrick]

You were close - it does not matter that Personal is hidden:

Please Login or Register  to view this content.

[Timmymac]

Many thanks for the reply Bernie......I'm still getting a debug screen however.

So...if I'm on Book1 cell C7 for example.....and I run the macro..I'm hoping to get a paste of the cells I have stored as a name within the Personal file as MODELTEMPLATE in my personal book beginning at C7 as a result. I've got the above in one line so I'm not sure what stage it's stopping me.

[Bernie Deitrick]

Let's break it down:

Msgbox Workbooks("PERSONAL.XLSB").Name
Msgbox Workbooks("PERSONAL.XLSB").Worksheets(1).Name
Msgbox Workbooks("PERSONAL.XLSB").Worksheets(1).Range("MODELTEMPLATE").Address

If it fails on the third one - it should fail on the third line - it may be that your named range is not on the first sheet, or the name is slightly different....

[Timmymac]

Apologies Bernie.......as soon as I read your comment about the named range not being on the first sheet......i knew it wasn't and the penny dropped.

that's fantastic thanks....works a treat now

[Bernie Deitrick]

That's great