PDA

View Full Version : Loop - usage



k_v_deepu
09-24-2008, 03:26 AM
hi friends

i have a code which has to run on 10 workbooks, now i want some suggestion on how to use the loop in the macro.
there are 25 workbooks in my folder i have to run this macro on 10 workbooks. Is there any possibility that i mention the 10 workbooks name in the for loop or while loop so that repeating of the same code for 10 times will be eliminated

Sheets(5).Select
repWB = ActiveWorkbook.Name
Workbooks.Open ThisWorkbook.Path & "\OSA Data\BRGG_M.XLS"
lnkWB = ActiveWorkbook.Name
Cells(1, 1).Select
For i = 5 To 11
Selection.End(xlDown).End(xlDown).Select
Range(Selection, Selection.End(xlDown).End(xlToRight)).Select
Selection.Copy
Selection.End(xlDown).Select
Workbooks(repWB).Sheets(i).Activate
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(0, 0).Select
rowNum = ActiveCell.Row
Range(Selection, Cells(rowNum, 9)).Select
'Selection.Interior.ColorIndex = 13
'Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
Selection.End(xlDown).Select
'ActiveCell.Offset(1, 0).Value = "SONSTIGE"
ActiveCell.Offset(1, 0).Select
Workbooks(lnkWB).Activate
Next
Workbooks(lnkWB).Close

Bob Phillips
09-24-2008, 03:35 AM
What is the code supposed to do, apart from opening many workbooks, what happens within each workbook?

k_v_deepu
09-24-2008, 03:42 AM
The above code runs on only one workbook i,e BRGG_M.XLS
i need to enhance the code so that it open's the other workbooks listed below one by one and run the above mentioned code for each workbook.
the workbooks are ALN_M.xls, ALN_R.xls, BIOZ_M.xls, FIT_M.xls, FIT_T.xls, HAHN_M.xls, KLLG_R.xls, KLLG_T.xls, KLLN_M.xls, KLLN_R.xls

Bob Phillips
09-24-2008, 04:11 AM
I know that ...


What is the code supposed to do, apart from opening many workbooks, what happens within each workbook?

k_v_deepu
09-24-2008, 04:18 AM
i have a "master template" workbook where all the details of the linked workbook has to be put.
Each linked workbook contains 7 Manufacturers details which has to be put in the appropriate sheets of the "master template" workbook

Bob Phillips
09-24-2008, 04:28 AM
Sorry, but I am still not getting it and that recorded code is garbage.

Tell me about the sheets, are they named as the manufacturere? IS the same on the Master, does it have 7 identically named sheets? Do we append the data or overwrite each run. Spell out the requirements, don't drip-feed me or I for one will lose interest.

k_v_deepu
09-24-2008, 05:18 AM
i have attached a sheet for your reference, this sheet contains 7 different detial's like
LEH >= 200 qm nach Key Accounts (ex. HD)
Metro EH >= 200 qm
Edeka Gruppe >= 200 qm
Markant >= 200 qm
Rewe Gruppe
Tengelmann Gruppe >= 200 qm
Restlicher LEH >= 200 qm (ex. HD)

in my "master template" workbook i have 7 sheets to put the above details
now i have to copy the data from range("A5:I15") from this sheet and paste in the sheet named "LEH" in the "master template"
similary data range ("A21:I31") has to be pasted in the "Metro" sheet of the "master details" likewise i have to do for all the 7 Details and 10 different manufacturers which is in 10 different workbooks.

hope u understood what i am trying to say

k_v_deepu
09-24-2008, 07:13 AM
can any one help me??

Bob Phillips
09-24-2008, 08:06 AM
Post the master and AT LEAST one more working workbook.

k_v_deepu
09-24-2008, 08:31 AM
master file

k_v_deepu
09-24-2008, 08:33 AM
work file