Syntax Error: Crosstab to Flat File Macro

clovisjim

New Member
Joined
Jul 8, 2016
Messages
14
I recently came across a macro designed to convert crosstabular data to database ready data. You can find the post here...

I've recently found a macro that will allow me to convert crosstabular data to a data base format. (You can find it here http://nhsexcel.com/excel-pivot-table-crosstab-flat-list/)

I running to a syntax error at the beginning of the loop and can't figure out the issue. I don't know if it has to do with a different version of Excel, but I'd really love to use this macro. Any help would be appreciated.


Rich (BB code):
Sub CrossTabToList()'written by Doctor Moxie


Dim wsCrossTab As Worksheet
Dim wsList As Worksheet
Dim iLastCol As Long
Dim iLastRow As Long
Dim iLastRowList As Long
Dim rngCTab As Range 'Used for range in Sheet1 cross tab sheet
Dim rngList As Range 'Destination range for the list
Dim ROW As Long


Set wsCrossTab = Worksheets("Sheet1") 'AMEND TO SHOW SHEET NUMBER WITH THE CROSS TAB
Set wsList = Worksheets.Add


'Find the last row in Sheet1 with the cross tab
iLastRow = wsCrossTab.Cells(Rows.Count, "A").End(xlUp).ROW


'Set the initial value for the row in the destination worksheet
'I set mine as 2 as I want to put headings in row 1
iLastRowList = 2


'Find the last column in Sheet1 with the cross tab
iLastCol = wsCrossTab.Range("A2").End(xlToRight).Column


'Set the heading titles in the list sheet
'You will need to amend these to something appropriate for your sheet
wsList.Range("A1:F1") = Array("NAME", "PROJECT", "TYPE", "PLAN/ACTUAL", "WEEK", "HOURS")


'Start looping through the cross tab data
For ROW = 3 To iLastRow 'START AT ROW 3 AS THIS IS WHERE DATA BEGINS IN MY CROSS TAB
Set rngCTab = wsCrossTab.Range("A" & ROW, "C" & ROW) 'initial value A3 SETS THE RANGE TO INCLUDE ALL STATIC DATA - IN THIS CASE NAME, PROJECT, TYPE
Set rngList = wsList.Range("A" & iLastRowList) 'initial value A2


'Copy individual names in Col A (A3 initially) into as many rows as there are data columns
'in the cross tab (less 3 for Col A-C).
rngCTab.Copy rngList.Resize(iLastCol - 3)


'SELECT THE HEADING ROW WITH FORECAST/ACTUAL
'Move up ROW (INITIALLY 3) rows less TWO and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 2), 3).Resize(, iLastCol - 3).Copy
'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 3).PasteSpecial Transpose:=True


'SELECT THE ROW WITH THE WEEK NUMBERS
'Move up ROW (INITIALLY 3) rows less ONE and across 3 columns (using offset function). Copy.
rngCTab.Offset(-(ROW - 1), 3).Resize(, iLastCol - 3).Copy


'Paste transpose to columns in the list sheet alongside the static data
rngList.Offset(0, 4).PasteSpecial Transpose:=True


'Staying on same row (3 initially) copy the data from the cross tab
rngCTab.Offset(, 3).Resize(, iLastCol - 3).Copy


'Past transpose as column in list sheet
rngList.Offset(0, 5).PasteSpecial Transpose:=True


'Set the new last row in list sheet to be just below the last name copied
iLastRowList = iLastRowList + (iLastCol - 3)


'increment ROW by 1
Next ROW

End Sub

 

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Code:
Set rngCTab = wsCrossTab.Range("A" & ROW & ":C" & ROW)
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top