Wrong Copy Data from Several Excel file

[yamixas]

Dear all
Good day
i am having problem on copying data from 3 excel file to 1 master file.
i want to copy from source excel file to 1 master file
this is my code :

Sub CopyData()
Dim Fname As String
Dim scwbk As Workbook
Dim dswbk As Workbook
Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range

Set dswbk = ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
If Fname = "False" Then Exit Sub
Set scwbk = Workbooks.Open(Fname)
Set a = scwbk.Sheets("summary").Range("d4")
Set b = dswbk.Sheets("DF_data").Range("d4")
Set c = dswbk.Sheets("DF_data").Range("t4")
Set d = dswbk.Sheets("DF_data").Range("ar4")
If a = b Then

scwbk.Sheets("summary").Range("$d8:$d28").Copy dswbk.Sheets("DF_data").Range("$d8:$d28")
scwbk.Sheets("summary").Range("$e8:$e28").Copy dswbk.Sheets("DF_data").Range("$f8:$f28")
ElseIf a = c Then
scwbk.Sheets("summary").Range("$d8:$d28").Copy dswbk.Sheets("DF_data").Range("$t8:$t28")
ElseIf a = d Then
scwbk.Sheets("summary").Range("$d8:$d28").Copy dswbk.Sheets("DF_data").Range("$ar8:$ar28")
End If

scwbk.Close False

End Sub


at my highlighted code, the one i need to copy is from columns e(from source excel file) to columns f(at master excel file) but the data that been copied is from columns f(from source excel file)
hope someone can assist me with this problem
Thank you

 

[rlv01]

I don't see anything obviously wrong with your code, but you could give this cleaned up version a try.

Sub CopyData()
    Dim Fname As String
    Dim scwbk As Workbook
    Dim dswbk As Workbook
    Dim wsSum As Worksheet, wsData As Worksheet
    Dim a As Range
    Dim b As Range
    Dim c As Range
    Dim d As Range

    Set dswbk = ThisWorkbook
    Set wsData = dswbk.Worksheets("DF_data")

    Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
    If Fname = "False" Then Exit Sub

    Set scwbk = Workbooks.Open(Fname)

    On Error Resume Next
    Set wsSum = scwbk.Worksheets("summary")
    On Error GoTo 0

    If Not wsSum Is Nothing Then
        Set a = wsSum.Range("d4")
        Set b = wsData.Range("d4")
        Set c = wsData.Range("t4")
        Set d = wsData.Range("ar4")

        If a = b Then
            wsSum.Range("d8:d28").Copy wsData.Range("d8")
            wsSum.Range("e8:e28").Copy wsData.Range("f8")

        ElseIf a = c Then
            wsSum.Range("d8:d28").Copy wsData.Range("t8")

        ElseIf a = d Then
            wsSum.Range("d8:d28").Copy wsData.Range("ar8")
        End If
    Else
        MsgBox "Error - worksheet 'summary' not found", vbOKOnly Or vbCritical, Application.Name
    End If
    scwbk.Close False
End Sub

 

[yamixas]

Thank you for sharing a clearer version. it still not copy if a = c or d. If a=b the data will be copied from selected file. should i used the copy value only condition?

 

[Alex Blakenburg]

If you have formulas in the cells you are copying from then you most likely do want to copy values only.
However I don't think that is the main issue. I am wondering if you misunderstand how ElseIf works.
Since it is possible for a=b, a=c, a=d to all be true do you want them all actioned ? Currently if a=b the other 2 elseifs will not do anything (they are alternatives if the previous condition is not met)

 

[yamixas]

Thank you for your response. For my condition i would like to copy the formula also for checking purpose. I know the condition for elseif condition but for condition a=c, the copy condition will not happen. Sorry for inconvenience.

 

[Alex Blakenburg]

Before your close statement put the code below and show us what prints to the immediate window.

    If a <> b Then
        Debug.Print "a: "; a, "b: "; b, "c: "; c
    Else
       Debug.Print "a=b"
    End If

 

[jolivanes]

For some of us it is a lot easier if you explain in detail in a concise manner what you want to achieve.
You might be better off using Select Case.

 

[jolivanes]

If I understand your code right, this might work.

Sub CopyData()
Dim Fname As String
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim sh1 As Worksheet, sh2 As Worksheet
Set wb1 = ThisWorkbook
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
If Fname = "False" Then Exit Sub
Set wb2 = Workbooks.Open(Fname)
Set sh1 = wb1.Worksheets("DF_Data")
Set sh2 = wb2.Worksheets("summary")
    Select Case True
        Case sh2.Range("D4") = sh1.Range("D4")
            sh2.Range("D8:D28").Copy sh1.Range("D8")
            sh2.Range("E8:E28").Copy sh1.Range("F8")
        Case sh2.Range("D4") = sh1.Range("T4")
            sh2.Range("D8:D28").Copy sh1.Range("T8")
        Case sh2.Range("D4") = sh1.Range("AR4")
            sh2.Range("D8:D28").Copy sh1.Range("AR8")
        Case Else
    End Select
wb2.Close False
End Sub

 

[yamixas]

Thank you so much. using select help and solve the problem. i will try to learn more regarding select function. Thank you so much