Transfer Data from One Workbook to Another

Hi Trevor,

I am in the middle of something very similar to your project and facing the same issue.

I posted yesterday and received some valuable suggestions, take a look a this thread for starters
http://www.excelforum.com/excel-prog...ame-macro.html

I am still implementing it but I have seen previously that the guys who responded to my post are, in australian terms "blo*dy good" so I am confident it will work

Hi Trevor,

I do see the differences in need. As you could tell from my thread this is not my strength just yet.

Here is a block of code that I found on a thread many months ago. The code was posted by someone who had lifted if from some one else and didn't have a clue what it was doing.

One of the really senior forum members took the trouble to detail exactly what the code was doing (and I apologise to the author as I didn't record who made the comments)

It is about looping through workbooks so it may be of some guidance

'
'   this has some very useful explanations about workbooks
'

Sub WBLoop()
    Dim MasterWb As Workbook
    Dim tmpWb As Workbook
    Dim i As Long, iCount As Long
    Dim tmpDate As String, tmpStr As String
    
    ' Use the variable MasterWB to refer to the workbook that contains this code
    ' Must use Set because a workbook is an object. An object variable is effectively a pointer to an object,
    ' so MasterWb now will point to the same workbook as ThisWorkbook.
    Set MasterWb = ThisWorkbook

    ' For Each is a kind of a loop. It uses the variable tempWb as a workbook to cycle through every
    ' workbook that is currently open. Application.Workbooks is a Collection of all workbooks that are
    ' currently open
    For Each tmpWb In Application.Workbooks

        ' We want to ignore the remaining code for this workbook and for Personal.xls, so we check the name
        ' of tempWb and execute the code only if the name is not the name of this workbook and not Personal.xls
        If tmpWb.Name <> MasterWb.Name And _
           tmpWb.Name <> "Personal.xls" Then

            ' tmpStr is set to be the value found in cell A3 of the worksheet in the first tab of tmpWB
            tmpStr = tmpWb.Sheets(1).Range("A3").Value

            ' If the string is not empty and its length is >5 (redundant) then...
            If tmpStr <> "" And Len(tmpStr) > 5 Then

                ' set tmpDate equal to the substring of tempStr starting with the 6th character and ending at the character counted as the length
                ' of the string - 5. [It seems to be like there is a flaw in that logic because if the string has 6 characters, you can't go from character 6
                ' to character 1]. Then strip off any leading and trailing spaces.
                ' Judging by the name of the variable, perhaps we are expecting this to be  date
                tmpDate = Trim(Mid(tmpStr, 6, Len(tmpStr) - 5))

                ' If tmpDate is the same as the value in the named range "vDate" in Sheet1 of the active workbook then...
                If tmpDate = Sheet1.Range("vDate").Value Then

                    ' Set iCount to the number of nonblank cells in sheet 2014, range A3:Z3
                    iCount = WorksheetFunction.CountA(MasterWb.Sheets("2014").Range("A3:Z3"))

                    ' Copy the contents of Sheet1 A1:A5 of the active workbook to Sheet1 cell B3 of tmpWB in the loop
                    Sheet1.Range("A1:A5").Copy tmpWb.Sheets("Sheet1").Range("B3")

                    ' Use shortcut notation, now everything starting with a dot will be interpreted as referring to tmpWb
                    With tmpWb

                        ' Force a recalculation on the first sheet of tmpWB
                        .Sheets(1).Calculate

                        ' Cause the current workbook to be the active workbook
                        .Activate

                         ' Copy to the clipboard from the first sheet of tmpWB from B3 to the last used cell in column B
                        .Sheets(1).Range(Range("B3"), .Sheets(1).Range("B1000000").End(xlUp)).Copy
                    End With

                    ' Make this workbook the active workbook
                    MasterWb.Activate

                    ' For 1 to iCount (see note above for what iCount is), if the cell in this workbook, sheet 2014, row 3, column i, is equal to the
                    ' value of the named range vDate in Sheet1 of this workbook, then...
                    For i = 1 To iCount
                        If MasterWb.Sheets("2014").Cells(3, i).Value = Sheet1.Range("vDate").Value Then

                            ' paste the values from the clipboard into row 4, column i
                            MasterWb.Sheets("2014").Cells(4, i).PasteSpecial xlPasteValues
                        End If
                    Next i
                End If
            End If
        End If
    Next tmpWb
End Sub
'

jmac