Usage of the "Union" add-in
I've created a sales report in SMI. I also have a seperate static spreadsheet containing 500 rows of forecast data with the exact same columns that are in my SMI report. I am trying to use the Union add-in tool to draw the forecast data onto the RawData worksheet of my report at run time. It works, but the problem is that I end up with the forecast data at the top of my report having over-written the first 500 rows of actual sales data.
Is there a way to force the Union tool to place the data it is pulling in AFTER the final row of data that the actual SMI report is pulling? Or, alternatively, allow the Union tool to place its data at the top of the spreadsheet but have the rows that the SMI report is pulling from the database begin AFTER the last record pulled in from the static spreadsheet by the Union tool?
The Union add-in seems like it could be very useful if there was a way to tell it to place the data that it pulls in AFTER the last row of data that SMI pulls from the database, or, if there was a way to make the SMI report start writing its rows of data AFTER the data brought in by the Union tool.
Thoughts?
MAS 500 v7.30.5, Excel 2007 SP2
Views: 66
Replies to This Discussion
-
Permalink Reply by Ralph on -
At this point I don't think the Union add-in is the answer for what I'd like to accomplish.
What I'm wanting to do is very simple (although I don't yet know how to do it). My SMI report drops data onto a worksheet I call RawData. The Macro-enabled workbook that the report runs out to also has a worksheet called Budget that contains 500 rows of data with columns matching the columns that are on my RawData worksheet (column headings are absent on the Budget worksheet). All I want to do is create a macro that runs after SMI dumps its data out from our database that will copy (or move, doesn't matter) the entire active range of cells from the Budget worksheet to the RawData worksheet, and place this range of cells immedieately AFTER the final row of data on the RawData worksheet. Should be easy, right?
-
Permalink Reply by Ralph on
-
Well, until the VBA books I ordered get here, google is my friend. This seems to work for copying a range of data from a static worksheet to the bottom of a worksheet that has a variable number of records.
Sub CopyBudget()
Sheets("RawData").Select
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastRow = LastRow + 1
End If
Sheets("Budget").UsedRange.Copy
Sheets("RawData").Cells(LastRow, 1).PasteSpecial xlPasteValues
End Sub
-
Permalink Reply by Ralph on
-
So one problem with using a macro to add records to your raw data worksheet after the records that SMI itself pulls from the database is that using the "RawData" trick for the "Table/Range" field when inserting a pivot table results in the pivot table NOT including the records that were pulled into the worksheet with the macro. That sort of defeats the whole purpose of what I was trying to do. Anyone know of a workaround?
-
Permalink Reply by Ralph on
-
After including a macro that brings some additional static rows of data onto the worksheet that SMI is dumping data onto from the database, I needed a way to address the entirety of the data on Sheet1 when inserting a pivot table, including the additional data I bring in with a macro.
The Alchemex variables "RawData" and "RawDataCols" work great for setting Sheet1 as your pivot table data source, as long as you're not doing what I am doing and adding rows to it with a macro. Since the ranges that those variables represent are set by SMI at run-time and only include the records that SMI itself dumps out to the worksheet, they will of course exclude any extra records brought in via a macro.
The solution in this scenario is to create a new Name, set its Scope to "Workbook," and enter this in its "Refers to" field: =OFFSET(Sheet1!$A$1,0,0,(COUNTA(Sheet1!$A:$A)+5),48). Then use that name for your Range when inserting your pivot table. My data happens to have 48 columns, so you'd need to adjust that to match your column count.
-
Permalink Reply by Stephen Coull on -
Ralph, a fantastic solution - thanks for sharing.
I would like to get the UNION add-in function working as it would be easier to use for those folk not familiar with VBA, like myslef. Will keep you all posted as to my progress.
-
Permalink Reply by Ralph on
-
Actually, in this solution that I provided, the "+5)" should not be needed in the OFFSET function. I think I needed it because of some quirk in my particular spreadsheet, but under normal circumstances it should not be needed. Just choose a column in your spreadsheet that does not contain any blank cells and it should work without the "+5)."
Follow Alchemex on twitter
Like Alchemex on facebook
© 2012 Created by Admin.
