Copy from one workbook into another using a file path name

Question

I am trying to run a macro using an existing workbook that 1) opens a different workbook I'll call other 2) copies values from other workbook 3) pastes values from other into existing workbook and 4) closes other workbook. Below is the code I have so far. Is there a way to better define or set y? My code works except I am not able to close other and I think if I had y better defined I could just use something like y.close. Thanks!

Dim x As Worksheet
Dim y As Worksheet

Set x = ActiveWorkbook.Sheets("File44")

Workbooks.Open (ThisWorkbook.Path & "\File44.xlsm")
Workbooks("File44.xlsm").Activate

Set y = ActiveWorkbook.Sheets("File44 - Detail")
LastRow = y.Range("A" & Rows.Count).End(xlUp).Row

y.Range("A15:CQ" & LastRow).Copy
x.Range("A2").PasteSpecial xlPasteValues

ActiveWorkbook.Close savechanges:=False

you need to get an workbook object than access the worksheets in it. Also give your variable names something meaningful so you can distinguish what they are. — Sorceri
– Sorceri, Commented Feb 22, 2016 at 21:22

findwindow · Accepted Answer · 2016-02-22 23:28:42Z

1

Untested

Sub Chris2015()

Dim other As Workbook
Dim fromWS As Worksheet
Dim toWS As Worksheet
Dim lastRow As Long

Set toWS = ThisWorkbook.Sheets("File44") 'pasting to this worksheet
Set other = Workbooks.Open(ThisWorkbook.path & "\File44.xlsm")
Set fromWS = other.Sheets("File44 - Detail")

lastRow = fromWS.Range("A" & fromWS.Rows.Count).End(xlUp).Row

toWS.Range("A2:CQ" & lastRow - 13).Value2 = fromWS.Range("A15:CQ" & lastRow).Value2 'hope I did math right XD

other.Close savechanges:=False

End Sub

answered Feb 22, 2016 at 23:28

findwindow

3,2081 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris2015 Over a year ago

Worked great. Thank you!

Collectives™ on Stack Overflow

Copy from one workbook into another using a file path name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related