Copy multiple workbooks to another workbook on a specific row

[Closed]
Report
Posts
1
Registration date
Monday March 30, 2015
Status
Member
Last seen
March 30, 2015
-
Hi Guys,

The below VBA code helps me to import all the workbooks from the specified path to the current workbook.

The code works perfectly

However,I want to tweak this code a little, so that I can place the code in 5th row of the current workbook

The below code helps me in placing the data one row below

Can anyone help me to change the code, to paste the data in 5th row of the current workbook.



Sub mergeworkbooks()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object


Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")


Set dirObj = mergeObj.Getfolder("C:\Users\admin\Desktop\SLO 23032015")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

Range("A65536").End(xlUp).Offset(2, 0).PasteSpecial


Application.CutCopyMode = False
bookList.Close
Next


Cells.Replace What:="Hong Kong", replacement:="HongKong", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False

Cells.Replace What:="China, Peoples Republic", replacement:="China", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False

Cells.Replace What:="Korea, Republic of (South)", replacement:="Korea", Lookat:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, searchformat:=False, _
ReplaceFormat:=False

End Sub