Copy from one workbook to two others with restrictions

[janlindgren]

Hi!

I have a problem that I've tried solving for quite some time, but with no success. I've tried adapting code for similar problems from this site but I've been unsuccessful.

I have three files for now named Original, Copy1 and Copy2. I have information in Original that I want to copy to Copy1 and Copy2. Now - I have some control over the file Original (I can add macros and change certain things, but not others) but I have virtually no control of Copy1 and Copy2. I'm not actually allowed to post copies of the files here, but I've created mock-up-versions that I'll attach at the end of the post to give you an idea of how they look.

Now - the copying part I've managed to solve, but there are some things I was hoping to pull off but I haven't been able to do that.

Since I don't have any control over Copy1 and Copy2 I was hoping to be able to use the labels of each column (for instance "location") in Original to find the corresponding columns in Copy1 and 2 because the labels are considerably more static than the placement of the columns. But as you can see the labels aren't exact matches and I can't change the labels in Original. I will always know the labels in Copy1 and Copy2 so there's always the possibility of just adding the labels to the code.

The columns don't match each other - so for instance Column B in Original might be in column C in Copy1 and column D in Copy2. And not all columns in Original will be in Copy1 or Copy2.

The information in one column in Original decides from what extra worksheet beyond the first one that gets its information copied to Copy1 and 2 along with the column in itself. In the original file there's about 40 worksheets to choose from, but I only made two for the mock-up. Copy1 and 2 only have one worksheet each. The names of the worksheets are not ""Sheet1" and so on, but have been renamed.

How many rows are supposed to be copied varies from time to time. Sometimes maybe 100 - other times maybe 1000 or more and all rows must be copied.

The names of all three files will change over time depending on what version it is - but the name change will be predictable. The real name of each file is more similar to "Copy1 V2 Location" or "Original-V9-Stuffs" with the digit after "V" being the only real difference between versions.

Is it possible to create a code which takes all this into account?

Orginal.xls
Copy1.xls
Copy2.xls

[StevenM]

Copy 1 has:

Please Login or Register  to view this content.

Copy 2 has:

Please Login or Register  to view this content.

And you want to copy information found in Original underneath these headings, yes?
Which information goes where?
Could there already be information under the copy's headings, so one would need to find the next blank row?
Once we match Original's headings with the copy headings, do you want all the data underneath Original's headings copied over?

[janlindgren]

Yes, precisely.
Parent to Parent Location; Location to Location; Description to Description and Location Description; Type/Model to Type and Model; Manufacturer to Manufacturer; Serial Number to Serial Number; Diameter to Diameter; Length to Length; Width to Width.
No - there should be no previous information in either Copy1 or Copy2
Yes, all data under the headings in Original must be copied.

[StevenM]

Your Original Workbook had "Serial Number" on sheet 2 and 3. (I ignored that problem.)
I created two sets of headers (separated by a comma).
I searched for the header in the copy worksheet, then used the corresponding header and searched for it in the Original
workbook.
Also, your copy workbooks had "Serial number" and not "Serial Number"; thus I used: MatchCase:=False to get either.
If this is not the case with your actual workbooks, you can change it to: MatchCase:=True

Obviously, the names of the copy workbooks will need to be changed. Indeed, that part of the code will need to be re-written since you have many workbooks which need to be copied.

Play around with it, and test it, see if this works for you.
If you have any questions, please ask.

This code should be copied and placed in a standard module of your original workbook.

Please Login or Register  to view this content.

[StevenM]

P.S. I noticed two mistakes. I corrected the code on the forum, but the code in the attachment doesn't have the corrections.

Please Login or Register  to view this content.

The corrections were:
Added + 1 in the line:

Please Login or Register  to view this content.

And Offset(1, 0) was added in:

Please Login or Register  to view this content.

[janlindgren]

Wow... I... this... I love this place! :D

Thank you for the reply!

I've played some with it, but will do some more playing.

One question: Why isn't Type/Model being copied from the original file to the copies?

And... one part of my problem is that some of the information will be found in different sheets. Umm... lets see if I can explain.

The files contain information about components - the main sheet has general information and the other sheets all have specific information for different types of components (for instance sheet2 might be valves and sheet3 might be sensors). But sometimes that specific information is still the same from sheet to sheet, which is why serial number was found in both sheet2 and sheet3. To try and mimic what I actually have to deal with.

[StevenM]

Let me explain what I did. It seemed to me that you have two sets of headers, and that they are paired up one with the other.

For example, in the original the header might say: Parent, but in one of the copies it might say Parent Location.
Then what I did, I searched for "Parent Location" in the copies, if I found it, I searched for "Parent" in the Original.
Then I copied the items underneath "Parent" to the area underneath "Parent Location."

Please Login or Register  to view this content.

Are you talking about the header "Type/Model" of the data underneath the header? (Because everything was copied on my worksheet examples.) One copy had "Type" and the other had "Model." I placed the data underneath "Type/Model" under both "Type" and "Model."

Perhaps to get a better look, run the following code on an empty worksheet.

Please Login or Register  to view this content.

This code will show how I paired up the headers.

If this method will work for you, then we can expand upon it. What I would need is a complete list of headers which might be used in a copy, and their equivalent header which is used in the Original.

If possible, it would be good if we could narrow down where we search for headers. I searched in the whole UsedRange.
And since your original had three sheets, and your copies only had one sheet, I searched only in one sheet for the copies and only in three sheets in the original. This, of course, can be modified.

[janlindgren]

Ah, now I see! Very weird - I downloaded your file and used it and everything worked fine except the Type/Model thing, but I realised that my version of both Copy1 and Copy2 had "Type/Model" in both instead of "Type" in one and "Model" in the second (which is the weird part - it shouldn't have been like that...). When I changed that I worked just fine. And yes - I was talking about the data underneath the header.

But with your great example (the last code) I now totally understand what you did and why it didn't work! It was trying to match "Type/Model" to "Type" and "Model" but just found "Type/Model" and thus it didn't work.

I really appreciate your help so far and I hope you don't mind if I try and solve the rest on my own and perhaps ask some more questions later if I get stuck - I don't want to be a bother and I really like figuring out thing on my own. :D

[StevenM]

Good luck!!

[janlindgren]

I got it working just great! Again you have my thanks!

But there is one small thing I don't really want to call a problem but still would like to fix. And although it's fast enough I wouldn't mind speeding it up a bit and of course getting an even better understanding of what I'm doing.

So... here we go.

The headers are always in the first 10 rows - could I simply change "Lookat:=xlWhole" to "Lookat:=xlPart" for that or does that mess it up somehow?

And... is it possible to use one of the columns to decide how many rows should be copied? One of the columns to be copied is always filled to row 5000, but all that information isn't necessary to copy - that column only needs to copy as much as the surrounding columns. What I'm thinking is setting one column as the "master" column that decides for all the other columns how many rows should be copied. This because every column (except the one with 5000 rows) should have exactly the same number of rows - if they don't then someone has messed up. The number does change from time to time though so I can't just say "10 rows" or "500 rows".

[janlindgren]

Nevermind about the rows - I found the solution myself. :D

[StevenM]

The headers are always in the first 10 rows - could I simply change "Lookat:=xlWhole" to "Lookat:=xlPart" for that or does that mess it up somehow?

Whole and Part have to do with whether you are trying to match the whole cell or only part of the cell.

What you want to change is the UsedRange, for example:

Please Login or Register  to view this content.

To something like:

Please Login or Register  to view this content.

Note: I picked "Z" out of thin air. If you want to be more precise, you will need to add a couple lines of code to find the last column.

And... is it possible to use one of the columns to decide how many rows should be copied? One of the columns to be copied is always filled to row 5000, but all that information isn't necessary to copy - that column only needs to copy as much as the surrounding columns.

If you knew that this column wouldn't be the first column to be copied, then you could just save the previous value and compare. But if there is a chance that this might be the first column to be copied, then you would need to find another column to look at.

[janlindgren]

Ah, that worked great. Thanks!

Yeah, it was the first column to be looked at. But column B has the most important information so I just got the code to check that column every time.