Copy data from certain cells on one workbook to another

[oo0tommyk0oo]

Hi,

I am trying to find the best way to copy data from certain cells (e.g.A3) on one workbook sheet (e.g. Workbook1.xls, sheet1) to specific cells (D1) on another (Workbook2.xls, sheet4). I started experimenting with VBA, but realised there were a few limitations to my knowledge.

The problems im coming accros are that workbook1.xls runs horizontally (data for 1 product all on one row, whilst workbook2 runs vertically (data for one product on 1 column) and there are gaps where no cells are copied. Also the values come from formulas.

I have attached examples.

Thank you for any help

Tommy

Update...

So this is what i have so far. Works for the 1st product, i was just wondering if there was a quicker way of copying this so it does it for all products or if i have to just copy it just changing the cell number of each product?

Thanks for any help

Please Login or Register  to view this content.

[jaslake]

Hi oo0tommyk0oo

Try this code in a General Module in Book 2. Book 1 and Book 2 must be in the same folder.

Please Login or Register  to view this content.

[oo0tommyk0oo]

Thanks this works great! just got it working with the test books. Could you give me a idea how i would update this to work with more columns. Basically instead of just 4 catorgories i have 18. Also would this work with different sheets? The final sheet Enter Material info, always stays the same (template) but the feeder book changes (or would i just have to change the name of each feeder)?

Thank you once again for your help

Especailly like the button.

Tom

p.s. Sorry for the late reply, was away for the weekend.

[jaslake]

Hi Tom

I've modified the code slightly as I was able to duplicate the error if both WB's were already open.

I'm attaching two new WB's for your download. Want to make certain we're looking at the same files.

Let me know of issues.

[oo0tommyk0oo]

That works great, with one open or both open. I noticed that it will look for unlimited products (rows from book1) I was just wondering how i would add more columns from book1? At the momment there are 4, in the working version i have 18. Im guessing i need to add to this part

For Each sCell In sRng
wsTarget.Range("B2").Offset(0, i).Value = sCell.Value
wsTarget.Range("B2").Offset(2, i).Value = sCell.Offset(0, 1).Value
wsTarget.Range("B2").Offset(4, i).Value = sCell.Offset(0, 3).Value
wsTarget.Range("B2").Offset(5, i).Value = sCell.Offset(0, 4).Value
i = i + 1

I have no problem editing it myself as there are so many, but could you please explain the wording (i.e. what i would change to add a new Column, and how to mark its destination on book2)

Thanks again

Tom

[jaslake]

Hi Tom

Your guess is right on. You need to add code as you described

Please Login or Register  to view this content.

and I see you picked up my double hit on

Please Login or Register  to view this content.

Good catch...made no sense...when I reviewed the code I wondered why I was doing that...didn't see it myself for a while.

Now, there may be a better way. I'll look at it but in the meantime, modify the code as you described. If you have issues, let me know. IF I find the better way, I'll let you know.

[jaslake]

Hi Tom

Didn't see this question...must have been between posts

Also would this work with different sheets? The final sheet Enter Material info, always stays the same (template) but the feeder book changes (or would i just have to change the name of each feeder?

I need more info regarding your environment and process

• Are ALL the Feeder Books ALWAYS the same and should they all be processed? If so, the procedure can loop through the Feeder Books or
• Should the User CHOOSE the Feeder Book? If so, a list of the Feeder Books can be presented to the user. The user would then choose the book and the procedure will run on the selected book.
• In either event, I'll need to know where these Feeder Books are located

[oo0tommyk0oo]

Hi, i did alot of editing on this post confused myself!

The code is working great now for all 18 columns, thank you for that. What i have is 6 or so workbooks all with the same table setup as the example workbook1. The data in these tables needs to be sent to another company who annoyingly have their own locked template workbook (workbook2). So at the end of each month i need to copy certain data (the code is working great for this) from the 6 workbooks to a template making 6 different workbooks to send to the company.

So if it is possible to get the macro to unlock the template workbook run and then lock again. Or if it will work even if it is not unlocked. The cells that the marco is copying too are not locked. Also as posted above. I have 6 different workbooks to run this code on. I have no problem copying the workbooks and renaming them just to run this script if that is the easiest way?

Hope this makes sense..

Thank you for all the help

Tom

[jaslake]

Hi Tom

Let me see in I understand. You have 6 or so Source Workbooks (Workbook 1) from which you need to create 6 or so Target Workbooks (Workbook 2).

The Target Workbook (Workbook 2) is a Client provided Template and the Template is "locked". What do you mean "locked"? Protected? If so, the Template can be unprotected, updated then protected (assuming you have the requisite password(s).

What's the naming convention for your Source Files? Are they all in the same folder? Will all 6 or so be processed or will you want to choose which to process?

What's the naming convention of the Template? Does the Template get populated or a copy of the Template? What's the naming convention of the these "new" files we just created. Where do they get saved?

Those are things that come to mind at the moment. Let me know.

[oo0tommyk0oo]

Sorry just read the bottom of your post

- The Feeder books always have the same format, but will be named differently. e.g. E06 MTS, E08 MTS
- If the user could choose the Feeder that would be great.
- The feeder books will prob be located in S:\HKM\BCH LEED (if this changes though aslong as i know where in the code to edit i can edit this.

[jaslake]

Hi Tom

This can be done

If the user could choose the Feeder

Get back to me on the other questions I raised in my post #9

[oo0tommyk0oo]

The Feeder files are named ***MTS.xls (***=E06, E07, D14 ect...) each is located in its own subfolder (e.g. S:\HKM\BCH LEED\E06\Material Tracking Sheet, S:\HKM\BCH LEED\E07\Material Tracking Sheet ect...)

The Template is named StantecMTS (Template).xls. and has no set location.

I thought the easist way would be to copy the template into the folder of the feeder that is being copied.

So copy StantecMTS (Template).xls into S:\HKM\BCH LEED\D07\Material Tracking Sheet and run macro. With the macro then saving the template as D07StantecMTS.xls or somthing simular in the same location.

I can get the password for the template. however all the cells inwhich the data is being copied are unprotected it is just a few formula cells which are protected to stop staff delelting them by accident (which they do alot!)

Hope this helps

and thank you so much for this!

[jaslake]

Hi Tom

It'll take me a while to sort this out. Get back to you.

[oo0tommyk0oo]

Thanks a lot, absolutely no rush, im thankful for any help.

Cheers

Tom

[jaslake]

Hi Tom

If I'm thinking correctly, I just realized, in our Sample Environment we have the code in Workbook 2, which, if I understand, is the Client's Template. AM I thinking correctly? How often to you get a new Template?

I'm thinking out loud here now but I'm thinking the code needs to be moved to a separate, independent, stand alone workbook (call it Macros.xlsm or give me a name).

[oo0tommyk0oo]

The template has been updated once. But there is a chance it will happend again. The update was just the deletion of one row.

By a seperate workbook do you mean one that takes data from feeder and then places it in the template? So no macro is needed on the protected template?

I will let you decide which is the best route

Name could be MTSmacro.xlsm

[jaslake]

This is what I mean

no macro is needed on the protected template

If the Template is updated, you lose all the code. Now, you may need to modify the code for Template updates but, if it's in a separate sheet, you'll modify it there.

This is the way I'm leaning. Post your current code so I don't have to update mine (I'll still look for a "better way" but, for the moment, we have something that works).

[oo0tommyk0oo]

Here is the code that i have, it works with my test templates which are exact copies of the originals without the protection and values instead of formulas.

Please Login or Register  to view this content.

Thanks once again

[jaslake]

Hi Tom

I see from your code that the Template layout is substantially different from Book 2. If it's possible, without violating any privacy issues, a copy of the Template would be nice. If it's a problem, I understand. If you feel more comfortable emailing the workbook, I can provide an address by PM.

Let me know how you feel about this.