Copying and pasting between 2 open files that are unnamed

[kknb4591]

Hi I have searched hi and low for an answer to this and would greatly appreciate any sort of assistance.

I have tried the write the below code to open up workbook1, delete the first 18 rows, then open up workbook2, delete the first 19 rows of that and then select the contents of workbook2, copy them, then switch to back to workbook1, find the last empty row and paste.

Problem is my vba opens the 2 workbooks from file paths entered into a separate macro enabled workbook.

So at the moment it looks like this but is breaking when it comes to the first workbook switch:

Workbooks.Open Filename:= _
Range("D6") 'file path stored in D6
Rows("1:18").Select
Range("A18").Activate
Selection.Delete Shift:=xlUp
??? how do I switch to this file???
Workbooks.Open Filename:= _
Range("D11") 'file path stored in D11
Rows("1:18").Select
Range("A19").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
??? then need to switch back to first file???
'Range("A65536").End(xlup).Offset(1,0)
ActiveSheet.Paste

Many thanks for any advice.

 

[shg]

Try this:

Sub x()
    Dim wks1        As Worksheet
    Dim wks2        As Worksheet
    Dim wks3        As Worksheet
 
    Set wks1 = ActiveSheet
 
    Workbooks.Open Filename:=wks1.Range("D6").Value
    Set wks2 = ActiveSheet
    wks2.Rows("1:18").Delete
 
    Workbooks.Open Filename:=wks1.Range("D11").Value
    Set wks3 = ActiveSheet
    wks3.Rows("1:19").Delete
    wks3.Range("A1", wks3.Range("A1").End(xlDown)).Copy _
            Destination:=wks2.Cells(wks2.Rows.Count, "A").End(xlUp).Offset(1)
End Sub

 

[kknb4591]

Many many thanks that worked great.

I changed the range on wks3 as I need the copy and paste all the rows to wks2.

How would I close down wks3 and then do my delete duplicates in wks 2? I tried the below but it is deleting duplicates in wks 3 instead.

wks3.Rows("1:1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy _
Destination:=wks2.Cells(wks2.Rows.Count, "A").End(xlUp).Offset(1)

?? need to close wks 3 here??

?? and then delete dupes in wks2 here (based on values in col 16)??

Dim LastRow As Long
With ActiveSheet 'tried putting wks2.ActiveSheet here, didn't work
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A1:BV" & LastRow).RemoveDuplicates Columns:=16, Header:=xlYes
End With

 

[shg]

How would I close down wks3

wks3.Parent.Close