Worksheet re-arrangement

[Malcolm Brown]

I have a Quickbooks report exported to Excel in the form of a multi-column
table. It takes the form of columns A to E being grouped nominal codes and
columns F to X bening cost-centres. What I need to do is bring the figures
into one vertical column. So there would be the nominal codes repeated
vertically for each cost-centre with all the figures in column F.

Is there an easy way to do this please without all the cutting and pasting
which could lead to terrible mistakes?

Thanks in advance.

Malcolm Brown

 

[Bernie Deitrick]

Malcolm,

Run the macro below, and when asked, answer 5...

But, the figures will actually go into column G: column F will be the header
values from the top row.

HTH,
Bernie
MS Excel MVP


Sub MakeDataTableFromCrosstab2()

'By Bernie Deitrick

Dim myCell As Range
Dim newSheet As Worksheet
Dim mySheet As Worksheet
Dim i As Long
Dim j As Integer
Dim k As Long
Dim l As Integer
Dim mySelection As Range
Dim RowFields As Integer

Set mySheet = ActiveSheet
Set mySelection = ActiveCell.CurrentRegion
RowFields = Application.InputBox( _
"How many left-most columns to treat as row fields?", _
"CrossTab to DataBase Helper", 1, , , , , 1)
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
Application.DisplayAlerts = True
Set newSheet = Worksheets.Add
newSheet.Name = "New Database"
mySheet.Activate
i = 1
For j = mySelection(1).Row + 1 To _
mySelection(mySelection.Cells.Count).Row
For k = mySelection(1).Column + RowFields To _
mySelection(mySelection.Cells.Count).Column
If mySheet.Cells(j, k).Value <> "" Then
For l = 1 To RowFields
newSheet.Cells(i, l).Value = _
Cells(j, mySelection(l).Column).Value
Next l
newSheet.Cells(i, RowFields + 1).Value = _
Cells(mySelection(1).Row, k).Value
newSheet.Cells(i, RowFields + 2).Value = _
Cells(j, k).Value
i = i + 1
End If
Next k
Next j

End Sub

 

[Malcolm Brown]

Run the macro below, and when asked, answer 5...

Wow, thanks very much. It's about 20 years since I used Basic and I hadn't
realised that macros could be written like this. I must pick it up again.

Thanks again

Malcolm Brown