none
data transfer in excel RRS feed

  • Question

  • i have this kind of data in excel file

    Exportfile.xlsx

          A        B
    1)    Head  31-Dec-2017
    2)    a         23
    3)    b         45
    4)    c         46

    Datasheet.xlsx

    And in datasheet data like this

    1)           A
    2) QUARTER          Data
    3) 31 Mar 2017  
    4) 30 Jun 2017  
    5) 30 Sep 2017  
    6) 31 Dec 2017  
    7)  
    8)  
    9)         B   
    10) QUARTER          Data
    11) 31 Mar 2017 
    12) 30 Jun 2017 
    13) 30 Sep 2017 
    14) 31 Dec 2017 
    15) 
    16) 
    17)         C  
    18)QUARTER          Data
    19) 31 Mar 2017 
    20) 30 Jun 2017 
    21) 30 Sep 2017 
    22) 31 Dec 2017 

    now i want to paste values from exportfile to datasheet and want data like this

    1)           A 
    2) QUARTER          Data
    3) 31 Mar 2017  
    4) 30 Jun 2017  
    5) 30 Sep 2017  
    6) 31 Dec 2017         23
    7)  
    8)  
    9)         B  
    10) QUARTER          Data
    11) 31 Mar 2017 
    12) 30 Jun 2017 
    13) 30 Sep 2017 
    14) 31 Dec 2017          45
    15) 
    16) 
    17)         C   
    18) QUARTER          Data
    19) 31 Mar 2017 
    20) 30 Jun 2017 
    21) 30 Sep 2017 
    22) 31 Dec 2017          46

    now i do this code

    Sub copy_data(File_Name)
       For i = 2 To 4
    Windows("Exportfile.xlsx").Activate
        Sheets("ESheet").Select
             Cells(i, 2).Select
         Selection.Copy
    Windows(File_Name).Activate
        Sheets("Dsheet").Select
       On Error Resume Next
      **j = '' here what i do** 
        Cells(j, 2).Select
       On Error Resume Next
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    Next
    End Sub

    now in above code on J line what should i do so values copy from export file and paste in datasheet in quarter 31 Dec 2017 through loop

    any solution ?









    Tuesday, May 1, 2018 1:29 AM

All replies

  • any solution please
    Tuesday, May 1, 2018 5:02 PM
  • Hi,

    I have downloaded your file, but I am not sure what your expected result is.

    Could you share us your expected result in "OF.xlsx"? And share us the logic to get the expected result.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 2, 2018 5:22 AM
  • check my updated question please
    Wednesday, May 2, 2018 5:32 AM
  • Based on my understanding, if you want to update the A,B,C block data based on "Exportfile.xlsx", you would be better to split the A,B,C blocks into different sheets. Becase, A,B,C blocks are changed, it is not statable to locate the right place to set value for "31 Dec 2017".

    Then, you will not need to loop through cells, just find the "31 Dec 2017" to set value like below:

    Sub copy_data()
       Dim exportWokrbook As Workbook
       Dim ofWorkbook As Workbook
       Dim targetRange As Range
       Dim targetSheet As Worksheet
       Set exportWokrbook = Workbooks("Export.xlsx")
       Set ofWorkbook = Workbooks("OF.xlsx")
       For i = 2 To 4
           On Error Resume Next
           Set targetSheet = ofWorkbook.Sheets(exportWokrbook.Worksheets("Sheet1").Range("A" & i).Text)
           If Not targetSheet Is Nothing Then
                   Set targetRange = targetSheet.Cells.Find(What:=exportWokrbook.Worksheets("Sheet1").Range("B1"), After:=ofWorkbook.Sheets("A").Range("a1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
            targetRange.Offset(0, 1).Value = exportWokrbook.Worksheets("Sheet1").Range("B" & i)
    
           End If
           
       Next i
    End Sub


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 2, 2018 8:41 AM
  • this is not possible to create separate sheet for A,B nad C 
    Wednesday, May 2, 2018 11:22 AM
  • If so, you need to check your business logic how to identify the three different "31 Dec 2017" are to "A,B,C".

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 3, 2018 5:37 AM
  • Hi rebmaamberrebmaambe,

    Have you fixed this issue yet? Please feel free to let us know if you still have the problem.

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 31, 2018 7:31 AM
    Moderator