Macro to copy cells from 1 workbook to another

**geff** · 09-04-2007, 06:32 AM

Hi there,

A while ago I created a very simple excel worksheet that opened up another more complicated excel workbook (master document) un-protected the document and "un-hid" specific sheets and then allowed the user to copy inputted data over to the other workbook. It would then allow the user at the touch of a button to re-protect the document and hide all the specific sheets etc.

Basically it allowed members of my team to input data into a master document without the risk of them screwing the master document up. Shhh don't tell them I said that!

The macros behind all that is below:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Now all that works fine....up until now! The above is all well and good as the document I am copying data into is a constant "Claim_v3.xls" and in a constant location. I now need to be able to select the document I wish to "Inject" into as a variable.

So....the "START INJECTOR" Macro needs to allow me to Open up any workbook using Windows explorer and browse and select the specific file. The password of the file to be opened will be constant as will the cell ranges that are being copied from and to. Just the name will change.

I will need the following code somewhere in the "START INJECTOR" macro:

Please Login or Register  to view this content.

But I don't know where to put it for a start and then how do make every referal in my current code to "Claim_v3.xls" actually refer to the workbook that has been opened.

Hopefully that makes sense. Thankyou in advance.

Regards
Geff

**royUK** · 09-04-2007, 07:06 AM

If you place your variable before any procedures within the module then it will be avilable to other Procedures within that module

Please Login or Register  to view this content.

**geff** · 09-04-2007, 07:18 AM

Wow speedy response, thankyou so much Roy.

I'm really sorry but could you spoon feed me a little. I'm not 100% where your code needs to go and how to use it.

As far as I can understand your defining the variable that is the workbook that is opened as "sWb" am I correct with that?

Using your code allows me to open up a workbook after which I can obviously unprotect and unhide. Where I get stuck is on the INJECT macro's. How do I "Activate" the "sWb" in order to paste over the cells that have been copied.

Once again thankyou, I notice your in Lincolnshire...I live in Sleaford myself...small world!

**royUK** · 09-04-2007, 07:40 AM

The code needs to be in the same module as the other Procedures, with the variable declared at the top. then amend your yoor other macro something like:

Please Login or Register  to view this content.

You don't need to Select or Activate ranges.

I live at Mablethorpe, so it is a very small world!

**geff** · 09-04-2007, 08:24 AM

Ahh right I think I may need to explain myself a bit better. The "INJECT" macros are assigned to buttons on my "Claim Injector". The user fills in the data and then presses the Inject button next to what they have filled in to copy and paste into the master document. So after each "Injection" it needs to reactivate the "Claim Injector".

On the "Claim Injector" there is a "Start" and "Finish" button at the top. The Start button runs the macro "START_INJECTOR" and the Finish button runs the "FINISH_INJECT" macro.

I have two queries in that case. Firstly using your code after I open the workbook, the "opened" workbook becomes the active workbook whereas I need it to revert to my "Claim Injector" also I need to run the:

Please Login or Register  to view this content.

Where do I need to place that code in your "openfile" macro?

My 2nd query is based on your last post. How does it know what cells to paste into on "swb"? If I use:

Please Login or Register  to view this content.

I get an runtime error '424' highlighting the line "sWb.Sheets("Project Information")

Thanks for your help so far.

**geff** · 09-05-2007, 03:41 AM

OK I still can't figure this out fully. All I can manage to do is re-activate my Claim Injector workbook. I can't get it to Un-protect or unhide. Any suggestions? I really appreciate all the help so far.

**broro183** · 09-05-2007, 04:41 AM

Hi all,

Roy, I hope I'm not stepping on your toes here but I thought I may as well help when I think I can (& it's a different time of day in your neck of the woods).
Geff, I haven't really tested all of the below so I hope it works - here goes... :-)

1)
To return the activation to the "injector" file (assuming that is where the macro is run from) use:

Please Login or Register  to view this content.

The way the variable has been declared ie

Dim sWb As String

means that a slightly different approach is needed:
[/CODE]workbooks(swb).Unprotect Password:="******"
[/CODE]

2)
This relates to how the variable has been declared too (you could change swb to "as workbook" but then you may need to make some other changes too.)

Please Login or Register  to view this content.

(Note that I've use the line continuation combination of space, underscore, & enter because this is one line of code, but Ooopps, this doesn't seem to work - I'm not sure why?)

A line that should work without the need for paste special is:

Please Login or Register  to view this content.

The exact destination is still needed & I think Roy may have just missed including the range on the end of his line of code.

hth
Rob

**royUK** · 09-05-2007, 05:12 AM

No problem Rob

Geff, here's a stab at your start macro.

Please Login or Register  to view this content.

The other macros could be a lot more efficient, you don't need all that activating.

**geff** · 09-05-2007, 06:02 AM

Roy,

I saw your post on MrExcel as well and have replied to it. Once again sorry for cross posting between forums I was not doubting the integrity of your answer simply trying to get as much help as I could get and boost my knowledge.

I now have a working solution and thankyou for all your help it is greatly appreciated. I hope I haven't caused offence.

Warmest Regards

Geff

Macro to copy cells from 1 workbook to another

LinkBack

Thread Tools

Rate This Thread

Display

Macro to copy cells from 1 workbook to another

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions