Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Alternative to search and replace filename

    I would like to pull data from the same cells from multiple worksheets; e.g. from cells g2 through j2 for files arm1.xls through arm180.xls, with all worksheets having the same sheet name. Other than a laborious search and replace, row by row, is there a way to bring in the correct filename for each row?

    First couple of rows have been done on attached spreadsheet.

    Thanks.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.E.,

    Do you want to maintian links to the original workbooks or only copy the data values?
    With this many workbooks to consolidate I would recommend coping values only.
    This can be accomplished with some simple VBA to loop through the file names and copy the data to successive rows.
    I'll bang out some code when I have your answer.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    copying values only, although I'd be interested in knowing how to maintain links, should I have a small data set in the future
    thanks for the help

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    J.E.,

    Here's some code that may do what you need.
    Code:
    Option Explicit
    
    Sub Consolidate()
    
       Dim wbTarget    As Workbook
       Dim wbSource    As Workbook
       Dim zWkBkToOpen As String
       Dim iCellCntr   As Integer
       Dim iCntr       As Integer
       Dim lRowCntr    As Long
       Dim iShtCnt     As Integer
       Dim bNoFile     As Boolean
       
       Application.ScreenUpdating = False
       Set wbSource = ActiveWorkbook
       lRowCntr = 2   'First data to be copied to row 2
       iShtCnt = 4    'Set equal to highest numbered workbook name.
       On Error GoTo NoFileErrorHandler
       
       For iCntr = 1 To iShtCnt
       
          bNoFile = False
          
          'Note: if worksheets are in different directory preceed arm
          '     with the d:\path\
          
          zWkBkToOpen = CurDir() & "\arm" & Format(iCntr) & ".xls"
          Set wbTarget = Workbooks.Open(zWkBkToOpen, , True)
          If Not bNoFile Then
            wbSource.Activate
            'Fill cells A:D with values from G:J
            For iCellCntr = 7 To 10
               Cells(lRowCntr, iCellCntr - 6) = wbTarget.Sheets(1).Cells(2, iCellCntr).Value
            Next iCellCntr
            wbTarget.Close
            lRowCntr = lRowCntr + 1 'Increment the row pointer
          End If  'Not bNoFile
          
       Next iCntr
       
       On Error GoTo 0   'Cancel Error Handler
       
    GoTo GetOut
    
    NoFileErrorHandler:
    
      If Err() = 1004 Then
        bNoFile = True
        Resume Next
      End If
      
    GetOut:
    
    End Sub   'Consolidate()
    Notes:
    1. This was developed with 2003 but should work with 2010 if you change the .xls reference to .xlsx also the consolidation sheet containing the macro must be of type .xlsm and stored in a "Trusted Location".

    2. Missing file names in the sequence will be ignored via the error handler.

    3. I've attached the sample .xls file I used you can just change the type to .xlsm via Save As.

    Post back with any questions.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    worked.
    Thanks!

Posting Permissions

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