none
VB Code for copy/paste data RRS feed

  • Question

  • Good morning,

    I am trying (without) any success to create a macro in order to transfer certain data between two specified dates from a series of sheets in one workbook into one list of data into another sheet in another workbook.

    The source workbook is called Tracabilitees_produits.  Within the source workbook there are 10 sheets named L1, L2….L10 (L to mean line for a production line).  Data in the machine sheets starts in row 1 with the titles, (day, workorder, date, input, article, output, product family, operator), with data starting from row 2.

    The workbook to be filled is called Base Extraction Heures TB, sheet Importation.  Cell C6 contains the date and time at which the start of the data to be copied should be considered and cell C7 the end date and time.  The format is 20/04/2017  15:42:43.  When the macro copies the data from each sheet (L1 for example) it needs to remember the sheet name in order to also paste this into the destination file as once all the data is listed we will need to know the corresponding machine.

    The code should then

    -          go to workbook Tracabilitees_produits, sheet L1,

    -          Record L1 from the sheet tab as the line being analysed

    -          find the row containing the start date and time, (lets call it StartRow),

    -          find the row containing the end date and time, (lets call it EndRow)

    -          count the number of lines between StartRow & EndRow (=NumberLines)

    -          select (B & StartRow : H & EndRow)

    -          copy

    -          go to Base Extraction Heures TB, sheet Importation

    -          paste data into A10:G downwards

    -          still in workbook Base Extraction Heures TB, sheet Importation fill inL1 (recorded at the start) in (H10: H (10+NumberLines))

    -          go to workbook Tracabilitees_produits, sheet L2,

    -          repeat the data selection by date and copy process  (StartRow & EndRow will be different on each machine so a reset is required)

    -          go to Base Extraction Heures TB, sheet Importation

    -          paste data into first empty row in col A downwards (needs to find first empty row as the last paste will have already filled in a part)

    -          go to workbook Tracabilitees_produits, sheet L3 and repeat until sheet L10 has also been copied.

    I guess this could be done via tables/arrays etc. in order to avoid going backwards and forwards through the data.  The amount of data to be transferred could be quite large depending on the time frame selected.

    Would someone be able to help by proposing a macro to do this operation?

     

    Thanks a million for your help.

    Wednesday, April 26, 2017 5:55 AM

All replies

  • Hi Lind89,

    I like to write VBA code (Excel) and make VB.NET applications, but I'm not good at English.
    Would you share sample files (workbooks) via cloud storage such as Dropbox, OneDrive, etc?
    __________
    Ashidacchi
    Wednesday, April 26, 2017 9:40 AM
  • Hi,

    Thankyou for offering but I am not sure the sample files will be that useful without the explanation that goes along with it.  There is probably a good chance of getting confused and as a result using up time to adapt the program several times - even I guess it is not that complicated for someone knowledgeable. 

    Wednesday, April 26, 2017 8:39 PM
  • Hi Linda,

    Thanks for visiting our forum.

    Then this forum mainly discusses general questions about Excel client. As your query is about VBA code, we'll move your thread to the following MSDN forum for Excel for better response:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, April 27, 2017 5:53 AM
  • Hello,
    Here is the example in VBA and you could adjust it for your need. The code runs in Importation sheet.

    Sub copytest()
    Dim startTime As Date
    Dim endTime As Date
    Dim sWorkbook As Workbook
    Dim sht As Worksheet
    Dim ws As Worksheet
    startTime = Range("C6").Value
    endTime = Range("C7").Value
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set sWorkbook = Application.Workbooks.Open("C:\Users\User4\Desktop\Tracabilitees_produits.xlsx")
    For Each sht In sWorkbook.Worksheets
    Dim startRow, endRow As Integer
    For i = 2 To sht.UsedRange.Rows.Count
    If sht.Range("C" & i) = startTime Then
    startRow = i
    ElseIf sht.Range("C" & i) = endTime Then
    endRow = i
    End If
    Next i
    If startRow > 0 And endRow > 0 Then
    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastrow = IIf(lastrow < 9, 9, lastrow)
    ws.Cells(lastrow + 1, 1).Value = "This is data from " & sht.Name
    Set rng = sht.Range("A" & startRow & ":G" & endRow)
    rng.Copy Range("A" & (lastrow + 2))
    End If
    Next sht
    sWorkbook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    End Sub
    

    Regards,

    Celeste


    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.

    Friday, April 28, 2017 8:16 AM
    Moderator
  • Hi Celeste, 

    Thanks for your help.

    I realised that the code cannot start by if cell Ci = startTime because the time part will not be exactly the same.  It needs a >= rather than the = which I modified.  The start time can be 15:42 or example and the code has to choose between 15:41 & 15:43 so it should start at 15:43.  Same applies for the end time.

    When I try to run the code it does not at all pick up the start and time end times.  By formula (if()) it works fine.

    Does the code needed modified to deal correctly with dates?

    Thanks for your help.

    Saturday, April 29, 2017 1:38 PM
  • Hi,
    I put all the index which row’s time between start time and end time into an array, and set first as start index and the last as end index.
    Here is the example.

    Sub copytest2()
    Dim startTime As Date
    Dim endTime As Date
    Dim sWorkbook As Workbook
    Dim sht As Worksheet
    Dim ws As Worksheet
    startTime = Range("C6").Value
    endTime = Range("C7").Value
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set sWorkbook = Application.Workbooks.Open("C:\Users\User4\Desktop\Tracabilitees_produits.xlsx")
    For Each sht In sWorkbook.Worksheets
    Dim startRow, endRow As Integer
    Dim index() As Integer
    Dim c As Integer
    c = 0
    For i = 2 To sht.UsedRange.Rows.Count
    If sht.Range("C" & i) >= startTime And sht.Range("C" & i) <= endTime Then
        ReDim Preserve index(c)
        index(c) = i
        c = c + 1
    End If
    Next i
    If c > 1 Then
    startRow = index(0)
    endRow = index(c - 1)
    End If
    If startRow > 0 And endRow > 0 Then
    lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastrow = IIf(lastrow < 9, 9, lastrow)
    ws.Cells(lastrow + 1, 1).Value = "This is data from " & sht.Name
    Set rng = sht.Range("A" & startRow & ":G" & endRow)
    rng.Copy Range("A" & (lastrow + 2))
    End If
    Next sht
    sWorkbook.Close SaveChanges:=False
    Application.ScreenUpdating = True
    End Sub

    Regards,

    Celeste


    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.

    Tuesday, May 2, 2017 6:26 AM
    Moderator