Macro: Code for skip a row

  • Thread starter Thread starter Albert
  • Start date Start date
A

Albert

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
 
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
 
Mike,
Thank you very much, simple, crude and beautiful, it does exactly what I want.
Keep Up the Good Work.

Albert
 
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
Click to expand...

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
Click to expand...

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

to create formula pointing to that cell
 
Thank you Dick,

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

Albert

Dick Kusleika said:
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
Click to expand...

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
Click to expand...

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

to create formula pointing to that cell
Click to expand...
 
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 said:
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
Click to expand...

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
Click to expand...

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

to create formula pointing to that cell
Click to expand...
 
Back
Top