Copy Cells from multiple workbooks

Purdue02

New Member
Joined
Nov 17, 2005
Messages
5
I am using the following code to open multiple workbooks and copy a range of cells into one workbook. It is currently copying B12: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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,216,086
Messages
6,128,734
Members
449,466
Latest member
Peter Juhnke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top