I'm at a loss here. I have a macro that stores two sets of data into a multidimensional array then opens a new workbook, and loops through the array placing data in cells. The problem I have is the first entry in the first dimension of the array is a duplicate of the first entry of the second dimension. Here's an image of the results:
cell A1 should actually be HD Supply, but for some reason it's being overwritten? Any help on why this is happening, and tips are greatly appreciated. I'm fairly new to VBA and Multidimensional arrays are somewhat foreign to me, so I think it's related to my pull function, and the setup of the multidimensional array.
Here's my code:
Option Explicit
'Variable Definitions ***********************************************************
Dim WorkbookSize As Long 'Var to hold number of rows in worksheet, used primarily to control loops
Dim wb As Workbook 'var to hold workbook object
Dim TempPath As String 'var to hold local temp path to store newly created workbook
Dim i As Integer 'var used as counter for loops
Dim c As Integer 'var used as counter for loops
Dim activeBook As String 'var to hold new workbook name
Dim values() 'array for pull data
'Main Driver
Sub Main()
'set current workbook as active workbook
Dim currentWorksheet As Worksheet
Set currentWorksheet = ActiveSheet
WorkbookSize = size(currentWorksheet) 'Run function to get workbook size
values = pull(currentWorksheet, WorkbookSize) 'Run sub to pull data
push create(), values
End Sub
'Get size of Worksheet
Function size(sh As Worksheet) As Long
size = sh.Cells(Rows.Count, "A").End(xlUp).Row
End Function
'Create workbook
Function create() As Workbook
Set wb = Workbooks.Add
TempPath = Environ("temp") 'Get Users local temp folder
With wb
.SaveAs Filename:=TempPath & "EDX.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
.ChangeFileAccess Mode:=xlReadOnly, WritePassword:="admin"
End With
Set create = wb
End Function
'pull data
Function pull(pullFromSheet As Worksheet, size) As Variant
Dim code() As Variant
ReDim code(size - 1, size - 1)
c = 1
For i = 1 To size
'Check code column for IN and Doctype column for 810
If pullFromSheet.Cells(i, 9).Value = 810 And pullFromSheet.Cells(i, 17).Value = "IN" Then
code(c - 1, 0) = pullFromSheet.Cells(i, 3).Value 'store in array
code(0, c - 1) = pullFromSheet.Cells(i, 18).Value 'store in array
c = c + 1
End If
Next i
pull = code
End Function
'push data to new workbook
Sub push(toWorkbook As Workbook, ByRef code() As Variant)
'activeBook = "TempEDX.xlsm"
'Workbooks(activeBook).Activate 'set new workbook as active book
Dim newSheet As Worksheet
Set newSheet = toWorkbook.Sheets(1)
Dim txt As String
For i = 0 To UBound(code)
newSheet.Cells(i + 1, 1).Value = code(i, 0)
newSheet.Cells(i + 1, 2).Value = code(0, i)
Next i
newSheet.Activate 'make new sheet active for the user
End Sub

cequals 1 (i.e. the first loop),code(c - 1, 0)andcode(0, c - 1)are bothcode(0, 0)