none
Convert Excel sheet that has data in crosstab format into list in another sheet RRS feed

  • Question

  • I have an export of data that is in excel as a crosstab table. I want to convert that to a list of data in another excel file using a macro

    the source data will always have the same format and filename, I am stuck on how to access the external excel file from my xlsm file with the code

    Sub ConvertTableToList()
        Const colINIT As String = "A"
        Dim WBS As Workbook  ' Source Workbook
        Dim WBL As Workbook ' Destination List workbook
        Dim WSS As Worksheet 'Source Sheet
        Dim WSL As Worksheet 'Destination List Sheet
        Dim i As Long, j As Long
        Dim iLastRow As Long
        Dim iLastCol As Long
       
        Set WBS = Workbooks("C:\Test\Source data.xlsx")
        Set WSS = WBS.Worksheets("Sheet1")
        Set WBL = Workbooks("C:\Test\Destination List.xlsm")
        Set WSL = WBL.Worksheets("Dest List")
       
       
        Application.ScreenUpdating = False
        With WBS
            iLastRow = .Cells(.Rows.Count, colINIT).End(xlUp).Row
            For i = iLastRow To 2 Step -1
                iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
                For j = iLastCol To 3 Step -1
                    WBL.Rows(i + 1).Insert
                    WBL.Cells(i + 1, 2).Value = .Cells(i, j).Value
                    WBL.Cells(i, j).Value = ""
                Next j
            Next i
            '.Rows(1).Delete
        End With
        Application.ScreenUpdating = True
    End Sub

    I'm getting errors on the object as well as using it in the loop. Please help

    Thursday, March 14, 2019 6:38 PM

Answers

    • Marked as answer by JHarding08 Thursday, July 25, 2019 5:28 PM
    Saturday, March 16, 2019 3:15 AM
  • Cells should refer to the worksheets, not to the workbooks:

    Sub ConvertTableToList()
        Const colINIT As String = "A"
        Dim WBS As Workbook  ' Source Workbook
        Dim WBL As Workbook ' Destination List workbook
        Dim WSS As Worksheet 'Source Sheet
        Dim WSL As Worksheet 'Destination List Sheet
        Dim i As Long, j As Long
        Dim iLastRow As Long
        Dim iLastCol As Long
       
        Set WBS = Workbooks.Open("C:\Test\Source data.xlsx")
        Set WSS = WBS.Worksheets("Sheet1")
        Set WBL = Workbooks.Open("C:\Test\Destination List.xlsm")
        Set WSL = WBL.Worksheets("Dest List")
       
        Application.ScreenUpdating = False
        With WSS
            iLastRow = .Cells(.Rows.Count, colINIT).End(xlUp).Row
            For i = iLastRow To 2 Step -1
                iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
                For j = iLastCol To 3 Step -1
                    WSL.Rows(i + 1).Insert
                    WSL.Cells(i + 1, 2).Value = .Cells(i, j).Value
                    WSL.Cells(i, j).Value = ""
                Next j
            Next i
            '.Rows(1).Delete
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JHarding08 Thursday, July 25, 2019 5:28 PM
    Thursday, March 14, 2019 9:10 PM

All replies

  • Does it help if you use

        Set WBS = Workbooks.Open("C:\Test\Source data.xlsx")

    and

        Set WBL = Workbooks.Open("C:\Test\Destination List.xlsm")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, March 14, 2019 8:09 PM
  • that does get me through that statement in the debugger,but setting the sheet gives me an error as does when I use the with statement
    Thursday, March 14, 2019 8:42 PM
  • Cells should refer to the worksheets, not to the workbooks:

    Sub ConvertTableToList()
        Const colINIT As String = "A"
        Dim WBS As Workbook  ' Source Workbook
        Dim WBL As Workbook ' Destination List workbook
        Dim WSS As Worksheet 'Source Sheet
        Dim WSL As Worksheet 'Destination List Sheet
        Dim i As Long, j As Long
        Dim iLastRow As Long
        Dim iLastCol As Long
       
        Set WBS = Workbooks.Open("C:\Test\Source data.xlsx")
        Set WSS = WBS.Worksheets("Sheet1")
        Set WBL = Workbooks.Open("C:\Test\Destination List.xlsm")
        Set WSL = WBL.Worksheets("Dest List")
       
        Application.ScreenUpdating = False
        With WSS
            iLastRow = .Cells(.Rows.Count, colINIT).End(xlUp).Row
            For i = iLastRow To 2 Step -1
                iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
                For j = iLastCol To 3 Step -1
                    WSL.Rows(i + 1).Insert
                    WSL.Cells(i + 1, 2).Value = .Cells(i, j).Value
                    WSL.Cells(i, j).Value = ""
                Next j
            Next i
            '.Rows(1).Delete
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by JHarding08 Thursday, July 25, 2019 5:28 PM
    Thursday, March 14, 2019 9:10 PM
    • Marked as answer by JHarding08 Thursday, July 25, 2019 5:28 PM
    Saturday, March 16, 2019 3:15 AM