I've written a fair amount of Excel VBA but am very new to Open Office macros. I need this quickly and hope someone can help me. Here is the scenario:
1. I have a data entry sheet which contains approximately 1600 detail lines, 1 record per row
2. I have a fairly complex print sheet which prints records similarly to labels, 2 sets of 5 across per page, left to right and top to bottom
The data for each individual record on the print sheet is spread out over 23 rows and 6 columns
3. The user indicates which data rows they want to print by placing an x in column A of the data entry sheet, usually around 30-50 rows from many non-contiguous areas of the sheet - row 5 and row 1595 are valid possible selections
What I have done is set up a staging area sheet which is the source for the print sheet. The print sheet will be populated by using the offset function to retrieve data from the staging area sheet. I need a macro that does the following:
1. Clear out the existing staging area data, preferable retaining the header row although that is not necessary if it makes the code a lot easier
2. Iterate over the data entry sheet and copy requested data into the next available row in the staging area sheet
Here is some pseudo VBA code if I were doing it in Excel
Code: Select all
With StagingAreaSheet
lEndRow = .Cells.SpecialCells(xlEndData).Row
lEndCol = .Cells.SpecialCells(xlEndData).Column
.Range(.Cells(2, "A"), .Cells(lEndRow, lEndCol)).Clear
End With
lNextStagingAreaRow = 2
With DataEntrySheet
.Activate
lEndRow = .Cells.SpecialCells(xlEndData).Row
For lRow = 2 To lEndRow
' So I can watch it while debugging.
.Activate
' Transfer the data to the staging area sheet.
If LCase$(Trim$(.Cells(lRow, "A").Value)) = "x" Then
' Save off data entry values.
sName = Trim$(.Cells(lRow, "B").Value
sAddress = Trim$(.Cells(lRow, "C").Value
' etc
With StagingAreaSheet
' So I can watch it while debugging.
.Activate
' Transfer data entry values to associated staging area values for use by print sheet.
.Cells(lNextStagingAreaRow, "D").Value = sName
.Cells(lNextStagingAreaRow, "B").Value = sAddress
' etc
lNextStagingAreaRow = lNextStagingAreaRow + 1
End With
End If
Next
End With
Bonus question 1 - I don't mind iterating over all of the rows in the detail sheet, but it is slow. In Excel, you could suck all of the values of a Range object into a variant array and process the array which is much faster. Is there equivalent functionality in Open Office?
Code: Select all
Dim vRow As Variant
With DataSheet
vRow = .Range(.Cells(lRow, "A"), .Cells(lRow, lEndCol)).Value
For lIndex = LBound(vRow) to UBound(vRow)
Next
End With
Thanks in advance for any ideas, suggestions or especially code samples