Posts by maehar

Option Explicit

Sub ListtoMatrixV2()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' From a Source WorkBook to Destination Workbook                                             '
' Ozgrid - 12 Dec 2022                                                                       '
' https://forum.ozgrid.com/forum/index.php?thread/1232926-automatically-creating-8x12-grids- '
' on-one-workbook-from-96-row-list-on-another-wo/                                            '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Long, j As Long
Dim lsrow As Long        ' start Source row
Dim lscol As Long        ' start Source col
Dim ldRow As Long        ' destination row
Dim ldCol As Long        ' destination col
Dim maxCols As Long      ' max nb Columns in Destination Matrix
Dim maxRows As Long      ' max nb Rows in Destination Matrix
Dim wkbsour As Worksheet ' define Source workbook and worksheet
Dim wkbdest As Worksheet ' define Destination workbook and worksheet
 
' To be adjusted to you actual situation for Workbook Names And Worksheet Names
' Make sure to show exact extensions such as .xlsm and xlsx
Set wkbsour = Workbooks("Test ListToMatrix (4).xlsm").Worksheets("Sheet1")
Set wkbdest = Workbooks("Final data.xlsx").Worksheets("Sheet1")
' Excel temporarely adjusted to speed-up process
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' To be adjusted to you actual situation
' Choose Destination Upper Left Location thanks to Cells(ldRow,ldCol)
ldRow = 5
ldCol = 2
' To be adjusted to you actual situation
' Choose Destination Matrix Size Rows and Columns
maxRows = 8
maxCols = 12
' To be adjusted to you actual situation
' Loop List in Source worksheet for example : starting Column B (i.e. 2) and starting Row 5
lscol = 2
lsrow = 5
    ' Loop through all Columns
    For j = 1 To maxCols
      ' Loop through all Rows
      For i = 1 To maxRows
        ' Build Matrix in Destination Workbook
            With wkbsour
                .Range(.Cells(lsrow, lscol)).Copy wkbdest.Cells(i + ldRow - 1, j + ldCol - 1)
            End With
            ' increment row
            lsrow = lsrow + 1
      Next i
    Next j
' Excel back to standard status
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox " Matrix has been created in Destination Workbook ..."
End Sub

Option Explicit

Private Sub ListtoMatrix()
' Generic Transforming List to Matrix
' Ozgrid - 25 Nov 2022
' https://forum.ozgrid.com/forum/index.php?thread/1232926-automatically-creating-8x12-grids-on-one-workbook-from-96-row-list-on-another-wo/

Dim i As Long, j As Long
Dim lsrow As Long       ' start Source row
Dim lscol As Long       ' start Source col
Dim ldRow As Long       ' destination row
Dim ldCol As Long       ' destination col
Dim maxCols As Long     ' max nb Columns in Destination Matrix
Dim maxRows As Long     ' max nb Rows in Destination Matrix

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Choose Destination Upper Left Cells(ldRow,ldCol)
ldRow = 5
ldCol = 2
' Choose Destination Matrix Size
maxRows = 8
maxCols = 12

' Loop List in Source Sheet1 - Column B - starting Row 5
lscol = 2
lsrow = 5
    For j = 1 To maxCols
      For i = 1 To maxRows
        ' Build Matrix in Destination Sheet2
        Sheet2.Cells(i + ldRow - 1, j + ldCol - 1).Value = Sheet1.Cells(lsrow, lscol).Value
        lsrow = lsrow + 1
      Next i
    Next j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox " Matrix created in Destination Sheet 2 "
End Sub