Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    New Lounger
    Join Date
    Sep 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    from multiple to one worksheet

    Hi Steve!
    What about copying a specific cell (say E6) from multiple .csv files to just one worksheet?
    I tried for hours and can't figure it out...

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

    I'm trying to apply the above example to paste the data in columns (not in rows) but the values are being pasted all in the same column. So I only can see the result from the last file copied.

    I'm using:
    ---------------------
    Option Explicit
    Sub CombineMultipleFiles()
    ' Path - modify as needed but keep trailing backslash
    Const sPath = "C:\MyPath\"

    Dim sFile As String
    Dim wbkSource As Workbook
    Dim wSource As Worksheet
    Dim wTarget As Worksheet
    Dim lColumns As Long
    Dim lMaxSourceColumn As Long
    Dim lMaxTargetColumn As Long
    'Dim blnNoHeader As Boolean

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wTarget = ActiveSheet
    lColumns = wTarget.Columns.Count
    sFile = Dir(sPath & "*.xlsx*")
    Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
    Set wSource = wbkSource.Worksheets("1")
    lMaxSourceColumn = wSource.Cells(lColumns, 1).End(xlUp).Column
    lMaxTargetColumn = wTarget.Cells(lColumns, 1).End(xlUp).Column
    wSource.Range("B5:B8").Copy _
    Destination:=wTarget.Cells(lMaxTargetColumn + 1, 2) 'to start column B
    wbkSource.Close SaveChanges:=False
    sFile = Dir
    Loop

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

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

    What I'm doing wrong?

    Many thanks

    LL

  3. #18
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Are you perhaps looking for this:

    Code:
    lMaxSourceColumn = wSource.Cells(1, lColumns).End(xlToLeft).Column
    lMaxTargetColumn = wTarget.Cells(1, lColumns).End(xlToLeft).Column
    Steve

  4. #19
    Lounger
    Join Date
    Apr 2014
    Posts
    36
    Thanks
    2
    Thanked 1 Time in 1 Post
    It's working fine!!

    Many thanks Steve

  5. #20
    New Lounger
    Join Date
    May 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve, I'm trying to only copy over the data from a table...Without the headers and the Macro buttons i've created is there a way to do that?

    this code is great, i really appreciate the help

    carney

  6. #21
    New Lounger
    Join Date
    Jul 2014
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear Steve,

    I'm a newbie for this forum. I'm looking for this kind of solution for my excel files and I found this post. Very useful.

    However I do need an extra function if is possible. I need that based on a condition in a specific column of each workbooks (common in the files that I have) the macro runs and give me the rows that I pretend.

    Example: I do have 12 files with the overtime of the employees during Jan to Dec (Column A - Name, Column B - Date, C - Nº Hours). I want to compile the information by selecting when Column A = John. The new file will give me the resume of John/hours during that year in a single file).

    Thanks for your help,
    Drsker

  7. #22
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I would recommend NOT putting them into 12 separate files. I would add a column with month to all the data and them combine them into 1 file. Then you can use autofilter to just see a particular persons data. You could use a pivot table (for example) to list by person (rows) and months(columns) the sum of the overtime.

    Steve

  8. #23
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve
    This is nearly what I am wanting. I need the formula to only import select(I have a list on the open workbook) files, not all the saved saved in the location. Can you please help me?

  9. #24
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Have you tried adapting the generic code in this thread? You don't provide specifics so I can only provide general responses...

    Steve

  10. #25
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sdckapr View Post
    Have you tried adapting the generic code in this thread? You don't provide specifics so I can only provide general responses...

    Steve

    Is this what you are after?
    Steve

    Code:
    Option Explicit
    Sub CopyWorksheets()
    ' Path - modify as needed but keep trailing backslash
    Const sPath = "C:\MyPath\"
    Dim sFile As String
    Dim wbkSource As Workbook
    Dim wSource As Worksheet
    Dim wbkTarget As Workbook

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set wbkTarget = ActiveWorkbook
    sFile = Dir(sPath & "*.xls*")
    Do While Not sFile = ""
    Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
    For Each wSource In wbkSource.Worksheets
    With wbkTarget
    wSource.Copy After:=.Sheets(.Sheets.Count)
    End With
    Next
    wbkSource.Close SaveChanges:=False
    sFile = Dir
    Loop

    ExitHandler:
    Application.ScreenUpdating = True
    Exit Sub

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


    I have used this code, but it brings all files saved in location. I want it to bring only the files listed in column A from my open workbook.

  11. #26
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Can you attach a sample workbook and explain exactly what you want to do?

    in a general sense, instead of looping with the DIR to look for folders on the drive, you would loop through the range of cells with the workbook name. The rest of the code would be the same, it is just a modification of the files you want to open.

    Again, If you want specific answers you need to provide specific questions...

    Steve

  12. #27
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Steve
    I have attached a file, its also has some screen shots. Thanks so much for your help
    Attached Files Attached Files

  13. #28
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    As I outlined earlier...

    Code:
    Option Explicit
    Sub CopyWorksheets()
    ' Path - modify as needed but keep trailing backslash
      Const sPath = "C:\Test\"
      Dim sFile As String
      Dim wbkSource As Workbook
      Dim wSource As Worksheet
      Dim wbkTarget As Workbook
      Dim wFiles As Worksheet
      Dim x As Long
      On Error GoTo ErrHandler
      Application.ScreenUpdating = False
      
      Set wbkTarget = ActiveWorkbook
      Set wFiles = ActiveSheet
      x = 0
      sFile = wFiles.Range("A2").Value
      Do While Not sFile = ""
        Set wbkSource = Workbooks.Open(Filename:=sPath & sFile, AddToMRU:=False)
        For Each wSource In wbkSource.Worksheets
          With wbkTarget
            wSource.Copy After:=.Sheets(.Sheets.Count)
          End With
        Next
        wbkSource.Close SaveChanges:=False
        x = x + 1
        sFile = wFiles.Range("A2").Offset(x, 0).Value
      Loop
      
    ExitHandler:
      Application.ScreenUpdating = True
      Exit Sub
      
    ErrHandler:
      MsgBox Err.Description, vbExclamation
      Resume ExitHandler
    End Sub

  14. The Following User Says Thank You to sdckapr For This Useful Post:

    MelDev (2014-07-27)

  15. #29
    New Lounger
    Join Date
    Jul 2014
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you so much, it is perfect

  16. #30
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You are very welcome.

    Steve

Page 2 of 2 FirstFirst 12

Posting Permissions

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