Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Malcolm Brown
 
Posts: n/a
Default Worksheet re-arrangement

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
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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" wrote in message
...
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



  #3   Report Post  
Malcolm Brown
 
Posts: n/a
Default

Bernie Deitrick wrote:

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copyright and worksheet protection dow Excel Discussion (Misc queries) 2 January 3rd 05 04:07 PM
Executing macro for all worksheet from a different worksheet Biti New Users to Excel 3 December 8th 04 11:05 AM
Reference Data in Moved Worksheet tommcbrny Setting up and Configuration of Excel 1 December 1st 04 07:49 PM
Linking items GREATER THAN O on another worksheet in the same Work Eddie Shapiro Excel Discussion (Misc queries) 4 December 1st 04 03:55 PM
Worksheet name and Backward compatibility Rich Excel Discussion (Misc queries) 3 November 30th 04 07:10 PM


All times are GMT +1. The time now is 04:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"