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 Before:=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
|
Similar Threads
