Results 1 to 5 of 5

Copy same range in many excel files and paste into a main file

Threaded View

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    brazil
    MS-Off Ver
    Excel 2007
    Posts
    64

    Copy same range in many excel files and paste into a main file

    Please, I need a little help.
    I want to copy a range from many excel files and paste into another one.

    All the files (with the ranges to be copied) are closed inside a folder, with sequential names .
    The file that will receive the ranges ("Boxx") is in another folder.

    I want to copy the range "B1:S5" and paste in "B1"(in "Boxx").
    Then, the next ranges should be pasted sequentially in B6, B11, B16, etc.

    The closest VBA code I've found and modified is here:

    
    Sub Themissedline()
    
    Dim lCount As Long
    Dim wbResults As Workbook
    Dim wbCodeBook As Workbook
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    
    On Error Resume Next
        Set wbCodeBook = ThisWorkbook
            With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "E:\DOWNLOADS\IIII"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            '.Filename = "*AAAA.xls"
            If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all
            'Open Workbook x and Set a Workbook variable to it
            Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
    
    
    wbResults.Worksheets("Plan1").Range("B1:S5").Copy
    wbCodeBook.Worksheets("Plan1").Range("B1" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
                            
    
                            wbResults.Close SaveChanges:=False
                        Next lCount
                    End If
            End With
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    
    End Sub
    What's the missed line??
    Last edited by dualaudio454252; 11-15-2014 at 05:28 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy/paste Range of worksheets of several files in a folder to a master file
    By mrjinx007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-18-2013, 01:02 PM
  2. Replies: 0
    Last Post: 04-13-2013, 10:45 AM
  3. Replies: 1
    Last Post: 03-28-2013, 02:49 PM
  4. Merging multiple Excel files into tabs of one excel file without having to copy and paste?
    By BrettRCourtney in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-13-2013, 03:43 PM
  5. Copy-Paste Macro of 3 columns from multiple excel files into one summary file
    By jpmaster53 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2012, 07:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1