Macro: Code for skip a row
 

In the following Macro to generate a report on a workbook. I would like to
have an empty row between the generated data rows.
Can I also assign a pointer to the cells e.g. " ActiveCell.Offset(iSheet -
1, 1) = Worksheets(iSheet).[b4].Pointer " instead of the value.


Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet - 1, 1) = Worksheets(iSheet).[b4].Value
ActiveCell.Offset(iSheet - 1, 3) = Worksheets(iSheet).[g31].Value
ActiveCell.Offset(iSheet - 1, 5) = Worksheets(iSheet).[m31].Value
ActiveCell.Offset(iSheet - 1, 8) = Worksheets(iSheet).[s29].Value
ActiveCell.Offset(iSheet - 1, 10) = Worksheets(iSheet).[s31].Value


Next iSheet
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thank you very much.

Albert

Macro: Code for skip a row
 

Hi,

It's not very elegant but try this

Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
x = 0
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
ActiveCell.Offset(iSheet + x - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet + x - 1, 1) = Worksheets(iSheet).[b4].Value
ActiveCell.Offset(iSheet + x - 1, 3) = Worksheets(iSheet).[g31].Value
ActiveCell.Offset(iSheet + x - 1, 5) = Worksheets(iSheet).[m31].Value
ActiveCell.Offset(iSheet + x - 1, 8) = Worksheets(iSheet).[s29].Value
ActiveCell.Offset(iSheet + x - 1, 10) = Worksheets(iSheet).[s31].Value
x = x + 1
Next iSheet
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Mike

"Albert" wrote:

In the following Macro to generate a report on a workbook. I would like to
have an empty row between the generated data rows.
Can I also assign a pointer to the cells e.g. " ActiveCell.Offset(iSheet -
1, 1) = Worksheets(iSheet).[b4].Pointer " instead of the value.


Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet - 1, 1) = Worksheets(iSheet).[b4].Value
ActiveCell.Offset(iSheet - 1, 3) = Worksheets(iSheet).[g31].Value
ActiveCell.Offset(iSheet - 1, 5) = Worksheets(iSheet).[m31].Value
ActiveCell.Offset(iSheet - 1, 8) = Worksheets(iSheet).[s29].Value
ActiveCell.Offset(iSheet - 1, 10) = Worksheets(iSheet).[s31].Value


Next iSheet
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Thank you very much.

Albert

Macro: Code for skip a row
 

Mike,
Thank you very much, simple, crude and beautiful, it does exactly what I want.
Keep Up the Good Work.

Albert



"Mike H" wrote:

Hi,

It's not very elegant but try this

Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
x = 0
For iSheet = 1 To ActiveWorkbook.Worksheets.Count
ActiveCell.Offset(iSheet + x - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet + x - 1, 1) = Worksheets(iSheet).[b4].Value
ActiveCell.Offset(iSheet + x - 1, 3) = Worksheets(iSheet).[g31].Value
ActiveCell.Offset(iSheet + x - 1, 5) = Worksheets(iSheet).[m31].Value
ActiveCell.Offset(iSheet + x - 1, 8) = Worksheets(iSheet).[s29].Value
ActiveCell.Offset(iSheet + x - 1, 10) = Worksheets(iSheet).[s31].Value
x = x + 1
Next iSheet
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Mike

Macro: Code for skip a row
 

On Tue, 31 Mar 2009 11:51:01 -0700, Albert
wrote:

In the following Macro to generate a report on a workbook. I would like to
have an empty row between the generated data rows.
Can I also assign a pointer to the cells e.g. " ActiveCell.Offset(iSheet -
1, 1) = Worksheets(iSheet).[b4].Pointer " instead of the value.


Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count

For iSheet = 1 to (ActiveWorkbook.Worksheets.Count * 2) Step 2

That should work for skipping a row

ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet - 1, 1) = Worksheets(iSheet).[b4].Value

ActiveCell.Offset(iSheet - 1, 1).Formula = "=" &
Worksheets(iSheet).[b4].Address

to create formula pointing to that cell

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Macro: Code for skip a row
 

Thank you Dick,

I'll try it later on at work and let you know.
And thx for the pointer.

Albert

"Dick Kusleika" wrote:

On Tue, 31 Mar 2009 11:51:01 -0700, Albert
wrote:

In the following Macro to generate a report on a workbook. I would like to
have an empty row between the generated data rows.
Can I also assign a pointer to the cells e.g. " ActiveCell.Offset(iSheet -
1, 1) = Worksheets(iSheet).[b4].Pointer " instead of the value.


Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count

For iSheet = 1 to (ActiveWorkbook.Worksheets.Count * 2) Step 2

That should work for skipping a row

ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet - 1, 1) = Worksheets(iSheet).[b4].Value

ActiveCell.Offset(iSheet - 1, 1).Formula = "=" &
Worksheets(iSheet).[b4].Address

to create formula pointing to that cell

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Macro: Code for skip a row
 

Hi Dick,
Thank you for the help, the "Skip Row" Code works very well.
As for the pointer, it gives me a return value of "zero" and the cell
content, (what you see on the formula bar when you highlight the cell) is:
$B$4 for instance.
I'll tinker with it and see if I can come up with anything useful. the main
problem has been solved thanks to you and Mike.
Again, thank you for your help.
Albert


"Dick Kusleika" wrote:

On Tue, 31 Mar 2009 11:51:01 -0700, Albert
wrote:

In the following Macro to generate a report on a workbook. I would like to
have an empty row between the generated data rows.
Can I also assign a pointer to the cells e.g. " ActiveCell.Offset(iSheet -
1, 1) = Worksheets(iSheet).[b4].Pointer " instead of the value.


Sub WorkbookReport()

Application.ScreenUpdating = False 'DMcRitchie 2000-10-24
Application.Calculation = xlCalculationManual
Dim iSheet As Long
For iSheet = 1 To ActiveWorkbook.Worksheets.Count

For iSheet = 1 to (ActiveWorkbook.Worksheets.Count * 2) Step 2

That should work for skipping a row

ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name
ActiveCell.Offset(iSheet - 1, 1) = Worksheets(iSheet).[b4].Value

ActiveCell.Offset(iSheet - 1, 1).Formula = "=" &
Worksheets(iSheet).[b4].Address

to create formula pointing to that cell

--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com