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