Hi All!
I have several sheets in a wkbk where the user enters data in fixed cell
addresses. Cells are non contiguous. I want to read the values from each
sheet and cell and compile a tabulated summary report on a "Report" sheet in
the same wkbk. Each sheet results to be on a separate row.

Using office 2000
Thanks in advance for any assistance

sgl

Saved from a previous post:

This might get you started:

Option Explicit
Option Base 0
Sub testme01()

Dim historyWks As Worksheet
Dim curWks As Worksheet
Dim destRow As Long
Dim iCtr As Long

Dim myAddresses As Variant

myAddresses = Array("A1", "B1", "D1", "F1", "H1")

Set curWks = Worksheets("WorkSheetA")
Set historyWks = Worksheets("WorksheetB")

With historyWks
destRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With curWks
For iCtr = LBound(myAddresses) To UBound(myAddresses)
historyWks.Cells(destRow, 1 + iCtr).Value _
= .Range(myAddresses(iCtr)).Value
.Range(myAddresses(iCtr)).ClearContents
Next iCtr
End With

End Sub

If you don't want to clear the previous entry (if the entries are very similar,
it might be more useful to make that a manual effort), just comment/delete this
line:

.Range(myAddresses(iCtr)).ClearContents

(and change this line to reflect the cells you want copied--and keep them in
order. The first will go to column A, then column B, etc.)

myAddresses = Array("A1", "B1", "D1", "F1", "H1")

(no more than 256 cells--or you'll run out of columns!)


I'd plop a button from the Forms toolbar onto the worksheet (say A1 with the
window frozen to always show row 1. Then have the print range avoid row 1

or rightclick on that button
choose format control|Properties tab|and make sure "print object" is not
checked.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Change the worksheet names and fix the addresses on the Input sheet.

sgl wrote:

Hi All!
I have several sheets in a wkbk where the user enters data in fixed cell
addresses. Cells are non contiguous. I want to read the values from each
sheet and cell and compile a tabulated summary report on a "Report" sheet in
the same wkbk. Each sheet results to be on a separate row.

Using office 2000
Thanks in advance for any assistance

sgl

--

Dave Peterson

Sorry for the confusion of my posting my reply to you is in Excel Programming
- Thanks a million for your assistance

"Dave Peterson" wrote:

Saved from a previous post:

This might get you started:

Option Explicit
Option Base 0
Sub testme01()

Dim historyWks As Worksheet
Dim curWks As Worksheet
Dim destRow As Long
Dim iCtr As Long

Dim myAddresses As Variant

myAddresses = Array("A1", "B1", "D1", "F1", "H1")

Set curWks = Worksheets("WorkSheetA")
Set historyWks = Worksheets("WorksheetB")

With historyWks
destRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With curWks
For iCtr = LBound(myAddresses) To UBound(myAddresses)
historyWks.Cells(destRow, 1 + iCtr).Value _
= .Range(myAddresses(iCtr)).Value
.Range(myAddresses(iCtr)).ClearContents
Next iCtr
End With

End Sub

If you don't want to clear the previous entry (if the entries are very similar,
it might be more useful to make that a manual effort), just comment/delete this
line:

.Range(myAddresses(iCtr)).ClearContents

(and change this line to reflect the cells you want copied--and keep them in
order. The first will go to column A, then column B, etc.)

myAddresses = Array("A1", "B1", "D1", "F1", "H1")

(no more than 256 cells--or you'll run out of columns!)


I'd plop a button from the Forms toolbar onto the worksheet (say A1 with the
window frozen to always show row 1. Then have the print range avoid row 1

or rightclick on that button
choose format control|Properties tab|and make sure "print object" is not
checked.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Change the worksheet names and fix the addresses on the Input sheet.

sgl wrote:

Hi All!
I have several sheets in a wkbk where the user enters data in fixed cell
addresses. Cells are non contiguous. I want to read the values from each
sheet and cell and compile a tabulated summary report on a "Report" sheet in
the same wkbk. Each sheet results to be on a separate row.

Using office 2000
Thanks in advance for any assistance

sgl

--

Dave Peterson