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

 

[Oorang]

To provide code you would need to explain how you are choosing which specific rows in WB2 to copy and where they are pasted in WB1. Are the chosen by something like an account number in column A?

 

[toddykay]

Thank you for your reply. This is what makes it so difficult for me, there is nothing easy, it is basically actual rows and cells.

FILE: WB2-Sheet 1, copy d8-d33, Paste to WB1 cell c4
FILE: WB2-Sheet 1, copy h8-h33, Paste to WB1 cell c5
FILE: WB2-Sheet 1, copy m8-m33, Paste to WB1 cell c8
FILE: WB2-Sheet 1, copy t8-t33, Paste to WB1 cell c9

and so on for the 10 rows of sheet 1,

FILE: WB2-Sheet 2, copy d8-d33, Paste to WB1 cell c16
FILE: WB2-Sheet 2, copy h8-h33, Paste to WB1 cell c17
FILE: WB2-Sheet 2, copy m8-m33, Paste to WB1 cell c20
FILE: WB2-Sheet 2, copy t8-t33, Paste to WB1 cell c21

and so on for the 10 rows of sheet 2,

FILE: WB2-Sheet 3, copy d8-d33, Paste to WB1 cell c28
FILE: WB2-Sheet 3, copy h8-h33, Paste to WB1 cell c29
FILE: WB2-Sheet 3, copy m8-m33, Paste to WB1 cell c32
FILE: WB2-Sheet 3, copy t8-t33, Paste to WB1 cell c33

and so on for the 10 rows of sheet 3,


Looks like I need big help....

Kathryn

 

[Oorang]

From a business standpoint what determines what rows you need to copy?

 

[toddykay]

From a business standpoint what determines what rows you need to copy?

The workbook I am copying from is used by our Customer Service Centre to track all sorts of Performance Indicators, I am just selecting a portion of this data to report to the board.

For instance they want to see defects which is on line 4, cells D4 to O4, the headings (in cell A4) for this line are different in their spreadsheet and the boards so I cannot use that to find the data, or can I? Could I add their heading in to mine and search on that?

I just need a much easier way to extract the 10 lines of data I need from the Customer Service spreadsheet. If you could give me some ideas that would be helpful, I really do not want a very long winded macro that goes backwards and forwards loads of times, there must be a simpler way.

Many Thanks

Kathryn

 

[Oorang]

Will they always want the same data set? Is their sheet always in the same layout? Is your sheet always in the same layout? And of the two workbooks are you updating them continually or is it a new workbook for each cycle?

If the layout is always the same, just use the macro recorder, perform the task, save the macro and if you would like post it here and me or someone else will clean it up.

 

[toddykay]

Will they always want the same data set? Is their sheet always in the same layout? Is your sheet always in the same layout? And of the two workbooks are you updating them continually or is it a new workbook for each cycle?

If the layout is always the same, just use the macro recorder, perform the task, save the macro and if you would like post it here and me or someone else will clean it up.

Thank you for the reply.
YES - it is always the same data set
YES - always the same layout for both
Same workbooks are used

As suggested I recorded getting only 4 lines of data over 2 for 1 product and 2 for another, I need to get 10 lines for each 7 products. This is what I meant about the length of the macro if I did it all like this.

Many thanks for all your help.


Kathryn



Sub GetPerformanceData()
'
' GetPerformanceData Macro
' Macro recorded 21/11/2005 by KTodd
'

'
ChDir "H:\SUPCENTR\ProductPerformance"
Workbooks.Open Filename:="H:\SUPCENTR\ProductPerformance\prodperf2005.xls", _
UpdateLinks:=0
Sheets("Violet Boxer").Select
Range("D19:O19").Select
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C6").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Range("D39:O39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C10:N10").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Sheets("V-6").Select
ActiveWindow.SmallScroll Down:=-33
Range("D19:O19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C29").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Range("D39:O39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C33").Select
ActiveSheet.Paste
End Sub

 

[Oorang]

Sub GetPerformanceData()
Workbooks.Open Filename:="H:\SUPCENTR\ProductPerformance\prodperf2005.xls", UpdateLinks:=0
Sheets("Violet Boxer").Range("D19:O19").Copy
Windows("Balanced Scorecard.xls").Activate
Range("C6").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Range("D39:O39").Copy
Windows("Balanced Scorecard.xls").Activate
Range("C10:N10").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Sheets("V-6").Select
Range("D19:O19").Copy
Windows("Balanced Scorecard.xls").Activate
Range("C29").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Range("D39:O39").Copy
Windows("Balanced Scorecard.xls").Activate
Range("C33").Select
ActiveSheet.Paste
End Sub

 

[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.

Will they always want the same data set? Is their sheet always in the same layout? Is your sheet always in the same layout? And of the two workbooks are you updating them continually or is it a new workbook for each cycle?

If the layout is always the same, just use the macro recorder, perform the task, save the macro and if you would like post it here and me or someone else will clean it up.

Thank you for the reply.
YES - it is always the same data set
YES - always the same layout for both
Same workbooks are used

As suggested I recorded getting only 4 lines of data over 2 for 1 product and 2 for another, I need to get 10 lines for each 7 products. This is what I meant about the length of the macro if I did it all like this.

Many thanks for all your help.


Kathryn



Sub GetPerformanceData()
'
' GetPerformanceData Macro
' Macro recorded 21/11/2005 by KTodd
'

'
ChDir "H:\SUPCENTR\ProductPerformance"
Workbooks.Open Filename:="H:\SUPCENTR\ProductPerformance\prodperf2005.xls", _
UpdateLinks:=0
Sheets("Violet Boxer").Select
Range("D19:O19").Select
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C6").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Range("D39:O39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C10:N10").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Sheets("V-6").Select
ActiveWindow.SmallScroll Down:=-33
Range("D19:O19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C29").Select
ActiveSheet.Paste
Windows("prodperf2005.xls").Activate
Range("D39:O39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Balanced Scorecard.xls").Activate
Range("C33").Select
ActiveSheet.Paste
End Sub

 

[toddykay]

Thank you very much Oorang.

Kathryn