Automatically creating 8x12 grids on one workbook from 96 row list on another workbook

maehar · Nov 23rd 2022

Hello,

I am very new to macros and VBA but trying to improve. Each time I conduct an experiment a raw database is automatically generated. In the raw database each data set is arranged in 1 column with 96 rows (there are multiple columns of 96 on each raw data base). I need to transfer this data to another workbook which is my Final data base and on the final database I would like the data to be formatted in 8x12 grids (one for each column of 96). The way this is currently being done is very slow and error prone so I am trying to automate it.

For each RAW data workbook that is generated I would like to use a template of the final data workbook so that I can create 1 final data workbook which includes 8x12 grids for each column of 96 on the raw data sheet.

I initially used this formula "INDEX(A1:A96, TRANSPOSE(SEQUENCE(12,8)))" using excel 365 and that worked, but realised that the PC that I need to use the databases on has Excel 2019 which does not have the SEQUENCE function.

I am very new to macros and VBA but I was hoping someone could help me write a VBA code to create a function or a command button to do what I would like to do. (Hope it all makes sense - see attachments also)

Thank you in advance

**Carim** · Nov 23rd 2022

Hello,

You can achieve the same results with an Offset() function ...

See attached test file

Hope this will help

**Carim** · Nov 25th 2022

Hello,

Just realized you made it clear you had a preference for a macro ...

Attached is a demo file in order to transform a List into a Matrix ...

It will require to be adapted to your specific situation, and to the exact structure of your workbooks ...

To help you out, comments (-shown in green within the code-) should assist you for all your modifications ...

Hope this will help

**Carim** · Nov 26th 2022

maehar

Once you have tested both proposals :

1. the first one with a Function ...

and

2. the second one with a Macro ...

Feel free to share your comments ...

**Carim** · Nov 30th 2022

What would explain the total lack of reactions ... ???

maehar · Dec 5th 2022

Hi,

Apologies for the late reply, I have been very busy at work and I have not had time to look in detail at the options and try them out. Thank you for these options.

As soon as I have had a try I will leave my comments.

**Carim** · Dec 5th 2022

Quote from maehar

Hi,

Apologies for the late reply, I have been very busy at work and I have not had time to look in detail at the options and try them out. Thank you for these options.

As soon as I have had a try I will leave my comments.

Thanks for your reaction ...

maehar · Dec 5th 2022

Hi,

Apologies again for the late reply. I have had a look and the Matrix works perfectly, Thank you very much!

Do you know how/if I can still do this if my RAW data (list of 96) is in a different workbook to the final data (the 8x12 grid) that I want to output using the matrix (do I change the name of the location of the destination workbook?)

And how would I turn this into a workbook that I can use every time (as a template) to transform my data? After every experiment, the raw data is automatically generated from the software I use for the experiment and I generate the final data workbook myself. But I would like to have a template so that I can quickly transform it every time.

**Carim** · Dec 5th 2022

Glad to hear this could help you out

Your question about two separate workbooks would simply require following modification:

Code

Dim wkbdest As Workbook, wkbsour As Workbook
' Then you obviously need to Set the two new variables and change the instruction to 
wkbdest.Sheet1.Cells(i + ldRow - 1, j + ldCol - 1).Value = wkbsourc.Sheet1.Cells(lsrow, lscol).Value

Hope this will help

maehar · Dec 5th 2022

Hi,

Thank you. I have tried to make this modification. Ill attach a screen shot of the change I made. However, I get an error code "Subscript out of range".

I have probably done this wrong, could you please clarify?

Code:

**Carim** · Dec 5th 2022

Hi again,

When it is coming to your workbooks, you do need to think over the fact that

1. Both of them are already opened whenever you run your macro

or

2. Your source workbook contains an instruction, within your macro, to open the relevant workbook

Hope this clarifies

**Carim** · Dec 8th 2022

Hi,

Have you been able to fix this very last problem ...?

maehar · Dec 12th 2022

Hi,

Unfortunately not, I have the source and destination workbooks open when I try and run the command button, but I am getting this error code "Object doesn't support this property and method" so I think I am naming the file in the wrong way.

Also, similarly to the second part of my previous question. As a new raw data file is generated every time will I have to go into the VBA every time and change the file source and destination or is there a way I can automate this with a basic template workbook?

Thank you for your continued help. Much appreciated!

**Carim** · Dec 12th 2022

Hi again,

When copying a Range between opened workbooks using the Cells() method, you have to be cautious, since you must define both the workbook as well as the worksheet ...

see example below :

Code

Sub CopyRangetoWorkbookwithCells()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Copy Range Between Workbooks with Cells()
' ANY TIME you refer to a range with Cells() You Must Define Both the Workbook AND the Worksheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wkbsource       As Worksheet
Dim wkbdestin       As Worksheet
 
    Set wkbsource = Workbooks("Book1.xlsm").Worksheets("Sheet1")
    Set wkbdestin = Workbooks("Book2.xlsx").Worksheets("Sheet1")
 
    With wkbsource
            .Range(.Cells(lsrow, lscol)).Copy wkbdestin.Cells(I+ldRow-1, j+ldCol-1)
    End With
End Sub

Display More

Hope this clarifies (and will help...)

maehar · Dec 12th 2022

Hi,

Could you please show me how to integrate that into the original code?

Thank you

Code

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

Display More

**Carim** · Dec 12th 2022

Hello,

Since you are very new to macros and VBA ... should you need some more assistance ...

Do not hesitate to post your VBA code i.e. the whole of your macro ( BUT PLEASE NOT an IMAGE !!! )

so that all necessary modifications can be easily made ...

Cheers

P.S. Use the Code Tags shown "</>" in the menu ...

**Carim** · Dec 12th 2022

Hi again,

Below is the macro to be adjusted to your actual situation ... and to be tested :

Code

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("MySourceWorkbook.xlsm").Worksheets("Sheet1")
Set wkbdest = Workbooks("MyDestinationWorkbook.xlsx").Worksheets("Sheet2")
' 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

Display More

Hope this will help

**Carim** · Dec 13th 2022

After testing the macro, thanks for your feedback

maehar · Dec 13th 2022

Hello,

I have tested the macro and adjusted it to my situation but I am getting an error (on the part in red). The error is - Method 'Range' of object'_Worksheet' failed.

Thank you

Code

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

Display More

**rory** · Dec 13th 2022

Replace this:

Code

With wkbsour
.Range(.Cells(lsrow, lscol)).Copy wkbdest.Cells(i + ldRow - 1, j + ldCol - 1)
End With

with this:

Code

wkbsour.Cells(lsrow, lscol).Copy wkbdest.Cells(i + ldRow - 1, j + ldCol - 1)

I'd also suggest renaming the variable since it isn't a workbook object.

Automatically creating 8x12 grids on one workbook from 96 row list on another workbook

Participate now!

Share

Tags