Opening multilple workbooks

Purdue2479

Registered User.
Local time
Today, 13:14
Joined
Jul 1, 2003
Messages
52
I am using the following code to open multiple workbooks and copy a range of cells into one workbook. It is currently copying B12 to D12 and inserting values into A:C. I want to modify the code to just copy B12 & D12 and insert into columns A & B in new worksheet. I would also like to specify in what cell to start inserting values. Any suggestions would be appreciated.


Code:
Sub CopyRangeValues() 
    Dim basebook As Workbook 
    Dim mybook As Workbook 
    Dim sourceRange As Range 
    Dim destrange As Range 
    Dim rnum As Long 
    Dim i As Long 
    Dim a As Long 
    Dim MyPath As String 
    Application.ScreenUpdating = False 
    With Application.FileSearch 
        .NewSearch 
        .LookIn = "P:\" 
        .SearchSubFolders = False 
        .FileType = msoFileTypeExcelWorkbooks 
        ChDrive .LookIn 
        ChDir .LookIn 
        FNames = Dir("*AP AR*.xls") 
        If .Execute() > 0 Then 
            Set basebook = ThisWorkbook 
            rnum = 1 
            Do While FNames <> "" 
                Set mybook = Workbooks.Open(FNames) 
                Set sourceRange = mybook.Worksheets(1).Range("B12:D12") 
                a = sourceRange.Rows.Count 
                With sourceRange 
                    Set destrange = basebook.Worksheets(1).Cells(rnum, 1). _ 
                                    Resize(.Rows.Count, .Columns.Count) 
                End With 
                destrange.Value = sourceRange.Value 
                mybook.Close False 
                rnum = rnum + a 
                FNames = Dir() 
            Loop 
         End If 
            
            
    End With 
    Application.ScreenUpdating = True 
End Sub
 
Last edited:

Users who are viewing this thread

Back
Top Bottom