Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Get This Macro To Loop
I have the following piece of code that works fine:
Sub Test() ' ' Test Macro ' Macro recorded 01/24/2007 by RSabbara ' ' Sheets("Data").Select Range("C3").Select Selection.Copy Sheets("3rd Quarter 2006").Select Range("B31").Select ActiveSheet.Paste Sheets("3rd Quarter 2006").Select Application.CutCopyMode = False Sheets("3rd Quarter 2006").Copy Befo=Sheets(1) ActiveSheet.Name = ActiveSheet.Range("B31") Sheets("Data").Select End Sub The problem that I am having is that when I return to the data tab, I need Excel select cell C4 and so on until column C is blank, at that time, it will just stop. This code is used to dynamically create reports based on the template in in the sheet named 3rd Quarter 2006, I have been able to get as far as: Sub Test2() ' ' Open_File Macro ' Macro recorded 08/12/2006 by rsabbara ' ' Sheets("Data").Select Dim sWorkSheet As Worksheet Dim Path As String Dim iRowCounts As Integer Dim iRowCount As Integer Set sWorkSheet = ActiveWorkbook.ActiveSheet iRowCount = 2 'Starting row initialization iRowCounts = sWorkSheet.UsedRange.Rows.Count 'number of rows in active sheet For iRowCount = 2 To iRowCounts 'Loop through all. With ActiveWorkbook If sWorkSheet.Cells(iRowCount, 6) < "" Then 'sometime active sheet will have empty rows, so we don't have to process empty rows. Macro will fail if we attempt to process them ' Workbooks.Open Filename:=Path & sWorkSheet.Cells(iRowCount, 6) & ".xls" Application.Run "'Master_Rollup_file_v20070124.xls'!Test" ActiveWorkbook.Save ActiveWorkbook.Close End If If sWorkSheet.Cells(iRowCount, 6) = "" Then iRowCount = iRowCounts 'it comes out of the loop when it sees the empty rows End With Next iRowCount End Sub The problem is that when it loops back up, it still selects the same row. I am using MS Excel 2003. Help plese, Thank you, Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with using a Macro Loop | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) | |||
how can i loop a macro | Excel Discussion (Misc queries) | |||
macro loop | Excel Programming | |||
Help With loop macro?? | Excel Programming |