Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post

    Copy different columns from several files to 1 single column in 1 file

    Hello,

    I have several workbooks in severeal folders and I need to copy some ranges in different columns from each one of those files to ONE file only.

    I'have 2 problems:
    1) The data (the different column ranges) from each source book need to be pasted in the same column in the target book;
    2) Once, in the source book the data to copy are in formulas, I need to paste the values only.

    My code is:
    ------------------------------------------------------
    Option Explicit

    Sub CopyColumnsAllFiles()

    'Path - modify as needed but keep trailing backslash
    Const sPath = "D:\MyPath\"

    Dim sFile As String
    Dim wbkSource As Workbook
    Dim wSource As Worksheet
    Dim wTarget As Worksheet
    Dim wsName As String
    Dim lColumns As Long
    Dim lMaxSourceColumn As Long
    Dim lMaxTargetColumn As Long
    Dim lCopyRow As Long
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oSubFolder As Object

    lCopyRow = 1 'Set to Row where you want the first item in the column to be

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    Set wTarget = ActiveSheet
    lColumns = wTarget.Columns.Count

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    'Ask worksheet name to paste the info
    wsName = InputBox("Insert the sheet name")
    If wsName = vbNullString Then
    MsgBox "You cancelled!"
    Exit Sub

    End If

    For Each oSubFolder In oFolder.SubFolders
    sFile = Dir(oSubFolder & "\*.xlsx") 'UPDATE File name or part of it

    Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=oSubFolder & "\" & sFile, AddToMRU:=False)
    Set wSource = wbkSource.Sheets(wsName)

    lMaxSourceColumn = wSource.Cells(5, lColumns).End(xlToLeft).Column
    lMaxTargetColumn = wTarget.Cells(lCopyRow, lColumns).End(xlToLeft).Column

    wSource.Range("O4").Copy _
    Destination:=wTarget.Cells(lCopyRow, lMaxTargetColumn + 1).PasteSpecial(xlPasteValues)

    wSource.Range("N45:N48").Copy _
    Destination:=wTarget.Cells(lCopyRow, lMaxTargetColumn + 1).PasteSpecial(xlPasteValues)

    sFile = Dir()
    wbkSource.Close SaveChanges:=False

    Loop
    Next

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    ------------------------------------------------------

    Thanks in advance for any help

    Regards,

    LL

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you attach a sample file of your source and what you want the destination to look like. Also explain where you have issues with the code, error messages, bad logic, etc.

    One thing I note, which relates to your 2nd question is that while the format of copy with a destination (one line) works for a copy and a paste::
    Range1.copy destination:=range2
    which is equivalent to (since the underscore is just a line continuation indicator:
    Range1.copy _
    destination:=range2

    Copy with pastespecial is 2 lines:
    Range1.copy
    range2.pastespecial Paste:xlPasteValues

    I am not sure what you are looking for, but something more like:
    wSource.Range("O4").Copy
    wTarget.Cells(lCopyRow, lMaxTargetColumn + 1).PasteSpecial(xlPasteValues)

    may be more of what you want (change other ones as appropriate).

    Steve

  3. #3
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Steve,

    Thank you for getting back to me.

    Please find attached 2 woorkbooks:

    1) "Source_Workbook.xlsx"
    - Where I have the data to copy from
    - I will have 1 file for each technician

    2) "Target_Workbook.xlxs"
    - Where I need to copy the data to
    - The process will be done in daily basis

    Regarding the Paste Values problem: It's solved

    Source_Workbook.xlsxTarget_Workbook.xlsx

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you explain what you want to code to do with those 2 files exactly? Do you want to copy the source - grand totals to target - Day 1 in the appropriate column or those particular source - Day 1 into the target - day 1 locations? Or is it something else.

    Please be explicit.

    Steve

  5. #5
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    I need to copy the source from each day (day 1 and others) in to the target.

    The target file will have a sheet "day 1" will the source data from all technician, side by side in each column.


    LL

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What values in the source, to where in the target. The source is for John, will there be source files for the other names, etc. I presume that the source has 32 sheets (Day 1 thru Day 31 and a total. Will the target have all those days as well setup like the example worksheet?

    In detail, what do you want the code to do, what should the target file look like after the code is run? [Note it helps if you have sample data in the example files so that when testing we can see if the correct data transferred after testing.] Coding is very specific and one needs details...

    Steve

  7. #7
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    The values to be copied from the source (Tech code, Total A - 1, Total A - 2, Act 1, Act 2, Result 1, Result 2, etc) will be paste in to the target file in the correspondent lines.

    There will be a different source file for each technician (john, Mary and all the others).

    You're presuming OK because the source has 32 sheets as you said.

    The target file will be have a sheet for each day with the setup sent on the example file.

    If you need any furhter information, please ask.

    Many thanks for your help


    LL

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Do you just want example code for this particular transfer or should it more generic?

    Will the name of the technician always be in the cell J3 on each sheet?

    Should the code transfer all sheets 1 -31 or just the one for the current day of the month?

    [Should I presume that your target!B5:B13 all reference 1 row lower than they are supposed to?]

    Steve

  9. #9
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    If you telm me the code to copy from source and paste it on target I think that I can you it for all the other values.

    Yes, the technician will be always in J3.

    The code should tranfer only the sheet asked in the script commented as 'Ask worksheet name to paste the info.

    In the Target!B5:B13 will have text only; in the target example file send it had some formulas because I've copied form the other file; but the values will be pasted starting on column C


    LL

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a snippet of the code that I think does what you want. I have assumed you have already defined as variables the worksheets wSource and WTarget using the wsName variable you ask for at run time before this code runs.

    Code:
    'Move with other variable definition
      Dim iColTech As Integer
      Dim sTech As String
      Dim x As Integer
      Dim vRangeSource As Variant
      Dim vRowTargets As Variant
      
      'These are the ranges to copy from the source
      'The tech needs to be first, the order of the others does not matter, I put them in target row orders
      'Respectively: Tech name, Act 1-4, Total B-1, Results 1-4, Total A-1/2, Sales A-C
      vRangeSource = Array("J3", "K12:K15", "I9", "I12:I15", "C9:C10", "M12:M14")
      'These are the respective target rows for each of the source ranges
      vRowTargets = Array(4, 5, 9, 10, 14, 16)
      
    'Get the Tech name from the source and find the column of the target it is in
      sTech = wSource.Range(vRangeSource(0))
      iColTech = Application.WorksheetFunction.Match(sTech, wTarget.Rows(vRowTargets(0)), 0)
      
    'Loop through the respective ranges and pastespecial in the respective rows
      For x = 1 To UBound(vRangeSource)
        wSource.Range(vRangeSource(x)).Copy
        wTarget.Cells(vRowTargets(x), iColTech).PasteSpecial (xlPasteValues)
      Next
    Steve

  11. #11
    Lounger
    Join Date
    Apr 2014
    Posts
    37
    Thanks
    2
    Thanked 1 Time in 1 Post
    **SOLVED**

    Super!! It work's just perfect

    Many, many thanks Steve

    Regards

    LL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •