Help to Simplify routine

[toddykay]

Hello all

I will try to explain what I have been asked to do.

WORKBOOK 1 - contains 7 sheets of data,
each sheet relates to a Product
each sheet has exactly the same 80 rows and 17 columns, it is just the data that is different for each product.


WORKBOOK 2 - we need to copy part of 10 rows, (10 rows are not continuous and the cells in those rows are columns D to O), from each of the 7 sheets from workbook1 (the rows and columns we want to copy are all in the same place in the 7 sheets in workbook1).

These 10 rows are in exactly the same place in Workbook1 but are pasted to diferent rows in workbook2 as we need to perform further calculations on the data after extraction. In Workbook 2 we want all the data extracted to be on 1 sheet but still split by product.

I have struggled to make this simple to follow, looks like I am even having trouble writing what I need to do as well.


What would be the best way to do this, I can do very basic VB so I would tend to
open workbook1,
open workbook2,
select 1st product sheet in workbook2,
copy the 12 cells for 1st row,
go to workbook1 select cell and pasteSpecial,
go back to workbook2
select 1st product sheet in workbook2,
copy the 12 cells for 2nd row,
go to workbook1 select cell and pasteSpecial,

And so on, and so on for 10 rows, then write the whole thing again 6 more times for the other Product Sheets, this seems so long winded going backwards and forwards so many times, is there an easier way?

Many thanks for your help as usual.

Kathryn

 

[toddykay]

Could you explain a little further about "Mapping Table", this might make the job a little easier.

Many Thanks

Kathryn

 

[toddykay]

Hello tusharm

It isn't clear what it is you want to do. Each of the posts seem to change the definition of the task at hand. First, you mentioned 10 rows from each of 7 worksheets.

Then, you referred to d8:d33, h8:h33, etc. Those are columns each consisting of 26 cells! In the same example, you were pasting d8:d33 into C4 then the same range for the next worksheet into C16. How can one paste 26 cells (d8:d33) into 12 cells( C4:C15)?

Then, you referred to D4:O4 with a header in A4.

Then, you posted code that copied D19:O19 and D39:O39 from each of two worksheets and pasted the ranges into C6 and C10 for the first worksheet and C29 and C33 for the 2nd.

Maybe, you should take another look at what it is you should be doing. If it helps, consider posting sample data. In one of your posts you also mentioned something about headings in column 4. Even if they are different in the two workbooks, you may be able to create a 'mapping table' that relates the headings in the two workbooks. That would make it much simpler to find the correct rows to be copied.

So sorry for the confusion, I'll try my best to clarify with actual cell/rows.

Workbook 1 (sheet 1)
Copy the data from D33->O33, paste to Workbook 2 C5->N5
Copy the data from D19->O19, paste to Workbook 2 C6->N6
Copy the data from D35->O35, paste to Workbook 2 C9->N9
Copy the data from D39->O39, paste to Workbook 2 C10->N10

The above is only 4 lines, there are 10 rows from all sheets.

Then do it all over again another six times from Workbook 1 (Sheet2-7)

The data in all Seven sheets are in the same place for the copy.

We are pasting to the same sheet in Workbook 2 but different rows, same column C->N though.

I am looking for the best and most efficient way to do this

Many thanks

Kathryn

 

[tusharm]

OK, I'll try one more -- and last -- time.

You need to layout the *total* scheme of what goes where.

How do you know that for sheet1 row 33 goes into row 5? The same question applies to the other 3 rows you've mentioned.

Also, how does one know which are the other 6 rows of interest? And, where do they go?

Next, for sheet2. Where do each of the rows from this sheet go? And, how do you know that? Is there a pattern that can be applied to sheet3,..., sheet7?

As far as the mapping table goes, suppose your names in the source WB are s1,s2,s3... (in column 4) and in the destination worksheet are d1, d2, d3... Then, in the destination worksheet add another column with the names s1, s2, s3...Now, for each row in the destination worksheet, use the (new) column of sourcesheet names to find the correct row in the source workbook.

Hello tusharm
{snip}

So sorry for the confusion, I'll try my best to clarify with actual cell/rows.

Workbook 1 (sheet 1)
Copy the data from D33->O33, paste to Workbook 2 C5->N5
Copy the data from D19->O19, paste to Workbook 2 C6->N6
Copy the data from D35->O35, paste to Workbook 2 C9->N9
Copy the data from D39->O39, paste to Workbook 2 C10->N10

The above is only 4 lines, there are 10 rows from all sheets.

Then do it all over again another six times from Workbook 1 (Sheet2-7)

The data in all Seven sheets are in the same place for the copy.

We are pasting to the same sheet in Workbook 2 but different rows, same column C->N though.

I am looking for the best and most efficient way to do this

Many thanks

Kathryn