Please help me review my VBA code, it is not working

[lil mac]

[TABLE="width: 1508"]
<colgroup><col></colgroup><tbody>[TR]
[TD]I have use a variation of this code and it will work but now I have to search for data in more than two worksheets and my variation does not work. Error starts in the first "If Not UpdateCopyPrim" Please help me identify the error in my code.

Option Explicit[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Dim Values As New Collection[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Sub UpdateCopy()[/TD]
[/TR]
[TR]
[TD] Set Values = New Collection[/TD]
[/TR]
[TR]
[TD] If Not UpdateCopyPrim("ABC", "c11", "c12", "c13", "c17", "c22", "c23", "c27", "c28", "c29", "c30", "c31", "c32", "c25", "c24", "c35", "c36", "c37") Then Exit Sub[/TD]
[/TR]
[TR]
[TD] If Not UpdateCopyPrim("DEF", "c11", "c12", "c13", "c17", "c22", "c23", "c27", "c28", "c29", "c30", "c31", "c32", "c25", "c24", "c35", "c36", "c37") Then Exit Sub[/TD]
[/TR]
[TR]
[TD] If Not UpdateCopyPrim("GHI", "c11", "c12", "c13", "c17", "c22", "c23", "c27", "c28", "c29", "c30", "c31", "c32", "c25", "c24", "c35", "c36", "c37") Then Exit Sub[/TD]
[/TR]
[TR]
[TD] UpdatePaste[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Private Function UpdateCopyPrim(ByVal SheetNameOrIndex) As Boolean[/TD]
[/TR]
[TR]
[TD] Dim vFile As Variant[/TD]
[/TR]
[TR]
[TD] Dim wbCopyFrom As Workbook[/TD]
[/TR]
[TR]
[TD] Dim wsCopyFrom As Worksheet[/TD]
[/TR]
[TR]
[TD] Dim ThisAddress As Variant[/TD]
[/TR]
[TR]
[TD] On Error GoTo ExitPoint[/TD]
[/TR]
[TR]
[TD] vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _[/TD]
[/TR]
[TR]
[TD] "*.xl*", 1, "Select Excel File", "Open", False)[/TD]
[/TR]
[TR]
[TD] If VarType(vFile) = vbBoolean Then Exit Function[/TD]
[/TR]
[TR]
[TD] Set wbCopyFrom = Workbooks.Open(vFile, False, True)[/TD]
[/TR]
[TR]
[TD] Set wsCopyFrom = wbCopyFrom.Worksheets(SheetNameOrIndex)[/TD]
[/TR]
[TR]
[TD] For Each ThisAddress In WhichCells[/TD]
[/TR]
[TR]
[TD] Values.Add wsCopyFrom.Range(ThisAddress).Value, _[/TD]
[/TR]
[TR]
[TD] SheetNameOrIndex & "!" & ThisAddress[/TD]
[/TR]
[TR]
[TD] Next[/TD]
[/TR]
[TR]
[TD] UpdateCopyPrim = True[/TD]
[/TR]
[TR]
[TD]ExitPoint:[/TD]
[/TR]
[TR]
[TD] If Not wbCopyFrom Is Nothing Then wbCopyFrom.Close[/TD]
[/TR]
[TR]
[TD]End Function[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]Sub UpdatePaste()[/TD]
[/TR]
[TR]
[TD] On Error GoTo ExitPoint[/TD]
[/TR]
[TR]
[TD] Range("b3").Value = Values.Item("ABC!c11")[/TD]
[/TR]
[TR]
[TD] Range("b6").Value = Values.Item("ABC!c12")[/TD]
[/TR]
[TR]
[TD] Range("b7").Value = Values.Item("ABC!c13")[/TD]
[/TR]
[TR]
[TD] Range("b8").Value = Values.Item("ABC!c17")[/TD]
[/TR]
[TR]
[TD] Range("b11").Value = Values.Item("ABC!c22")[/TD]
[/TR]
[TR]
[TD] Range("b12").Value = Values.Item("ABC!c23") + Values.Item("ABC!c27") + Values.Item("ABC!c28") + Values.Item("ABC!c29") + Values.Item("ABC!c30")[/TD]
[/TR]
[TR]
[TD] Range("b13").Value = Values.Item("ABC!c24") + Values.Item("ABC!c25")[/TD]
[/TR]
[TR]
[TD] Range("b14").Value = Values.Item("ABC!c32")[/TD]
[/TR]
[TR]
[TD] Range("b15").Value = Values.Item("ABC!c31")[/TD]
[/TR]
[TR]
[TD] Range("b19").Value = Values.Item("ABC!c35") + Values.Item("ABC!c36")[/TD]
[/TR]
[TR]
[TD] Range("b20").Value = Values.Item("ABC!c37")[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] Range("c3").Value = Values.Item("DEF!c11")[/TD]
[/TR]
[TR]
[TD] Range("c6").Value = Values.Item("DEF!c12")[/TD]
[/TR]
[TR]
[TD] Range("c7").Value = Values.Item("DEF!c13")[/TD]
[/TR]
[TR]
[TD] Range("c8").Value = Values.Item("DEF!c17")[/TD]
[/TR]
[TR]
[TD] Range("c12").Value = Values.Item("DEF!c23") + Values.Item("DEF!c27") + Values.Item("DEF!c28") + Values.Item("DEF!c29") + Values.Item("DEF!c30")[/TD]
[/TR]
[TR]
[TD] Range("c13").Value = Values.Item("DEF!c24") + Values.Item("DEF!c25")[/TD]
[/TR]
[TR]
[TD] Range("c13").Value = Values.Item("DEF!c24")[/TD]
[/TR]
[TR]
[TD] Range("c14").Value = Values.Item("DEF!c32")[/TD]
[/TR]
[TR]
[TD] Range("c15").Value = Values.Item("DEF!c31")[/TD]
[/TR]
[TR]
[TD] Range("c19").Value = Values.Item("DEF!c35") + Values.Item("DEF!c36")[/TD]
[/TR]
[TR]
[TD] Range("c20").Value = Values.Item("DEF!c37")[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD] Range("d3").Value = Values.Item("GHI!c11")[/TD]
[/TR]
[TR]
[TD] Range("d6").Value = Values.Item("GHI!c12")[/TD]
[/TR]
[TR]
[TD] Range("d7").Value = Values.Item("GHI!c13")[/TD]
[/TR]
[TR]
[TD] Range("d8").Value = Values.Item("GHI!c17")[/TD]
[/TR]
[TR]
[TD] Range("d11").Value = Values.Item("GHI!c22")[/TD]
[/TR]
[TR]
[TD] Range("d12").Value = Values.Item("GHI!c23") + Values.Item("GHI!c27") + Values.Item("GHI!c28") + Values.Item("GHI!c29") + Values.Item("GHI!c30")[/TD]
[/TR]
[TR]
[TD] Range("d13").Value = Values.Item("GHI!c24") + Values.Item("GHI!c25")[/TD]
[/TR]
[TR]
[TD] Range("d14").Value = Values.Item("GHI!c32")[/TD]
[/TR]
[TR]
[TD] Range("d15").Value = Values.Item("GHI!c31")[/TD]
[/TR]
[TR]
[TD] Range("d19").Value = Values.Item("GHI!c35") + Values.Item("GHI!c36")[/TD]
[/TR]
[TR]
[TD] Range("d20").Value = Values.Item("GHI!c37")[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]ExitPoint:[/TD]
[/TR]
[TR]
[TD]End Sub[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

The code won't compile as the function UpdateCopyPrim only takes one argument, not multiple arguments like you are trying to pass in UpdateCopy.

What is the code actually meant to do?

 

[lil mac]

I have one workbook with multiple sheets, which new one is created monthly. I need to copy the data from that one into another fixed workbook, into other multiple sheets. I am starting with just pasting all the details into one worksheet, then start another macro to take that information from that one sheet into my second workbook with multiple sheets. The problem I run into is that I need specific cells to paste into different cells in a worksheet and some need to be added. Also, each month in my second workbook the information needs to go in a different column. I'm ok with running multiple macros to do this but also run the fewest possible. Since my VBA skills are limited, I'm not sure if my approach is the best (I'm questioning it as I write this)

The plan was to start with the macro to copy all the cells I need from the first workbook and then another macro to paste all the data I need in a new worksheet/workbook, this first part is what the code is doing. I was then going to take that worksheet and insert it in my second workbook and have it update each column.