Often on my journey with Dynamics GP I come across a situation, as we all do, where I think “Shouldn’t there be an easy/faster way to do this?”.
The answer is often “Yes” and there usually is, I just haven’t learned how to do it easier/faster yet. I had a situation where I had just finished using a Microsoft Word Mail Merge to create a Dynamics GP macro to set email settings on a long list of suppliers and I wanted to send a complete list of suppliers to a client in an email.
However, I had all of the information in one column and didn’t want to email a long list, but instead a table.
To google! I found this page on ExtendOffice.com
Multiple solutions on there, including a bit of VBA which will do this for you.
Like most things code, where possible I like to go through the solution to try and understand it, so I re-wrote the code manually. Here is my result based on the code on ExtendOffice.com.
Sub SplitData() Dim iRng As Range Dim oRng As Range Dim funcRow As Integer Dim funcCol As Integer Dim funcArray As Variant Set iRng = Application.Selection Set iRng = Application.InputBox("Input Data", "Data you would like to split", iRng.Address, Type:=8) funcRow = Application.InputBox("Rows Per Column", "How many rows do you want in each column?") Set oRng = Application.InputBox("Output Cell:", "Cell to start output", Type:=8) Set iRng = iRng.Columns(1) funcCol = iRng.Cells.Count / funcRow ReDim funcArray(1 To funcRow, 1 To funcCol + 1) For i = 0 To iRng.Cells.Count - 1 xValue = iRng.Cells(i + 1) iRow = i Mod funcRow iCol = VBA.Int(i / funcRow) funcArray(iRow + 1, iCol + 1) = xValue Next oRng.Resize(UBound(funcArray, 1), UBound(funcArray, 2)).Value = funcArray End Sub
For example, I have a column of numbers 1-100 in column A and want to split these into 15 rows per column. If I run the above VBA and in the prompts choose A1:A100 and then 15 columns output to B1, I get this:
As you can see, the information is transposed to 7 columns and leaves your original data intact in case you need it. Handy!