Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sgl
 
Posts: n/a
Default Read write non contiguous cells

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
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
sgl
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
OLAP write back via formulas (functions), not read only pivot tab BISability Excel Worksheet Functions 0 November 18th 04 05:15 AM
write a formula to sum cells on different pages of the same workb. Kestle Excel Worksheet Functions 1 November 8th 04 08:53 PM


All times are GMT +1. The time now is 09:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"