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