Need macro to iterate over rows and selectively copy some
Posted: Wed Sep 06, 2017 2:24 pm
Hello all,
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
I have already done the data entry to print sheet conversion for a different application, but in that case every data entry row needed to be printed so there was no need for a macro to transfer the requested data to the staging area. I have the print sheet set up with formulas to handle printing 1000 staging area rows. The staging area indicates how many print sheet pages of data there are so the user will just print pages 1-5, 1-21, etc based on what they have selected.
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?
Bonus question 2 - In Open Office and in my macro, could I filter on the x in column A and just process the 30-50 user selected rows?
Thanks in advance for any ideas, suggestions or especially code samples
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