Copy range from PriceList.xls to ClientPriceOffer-Company A.xlsm

[TorrO]

Hi

In book "ClientPriceOffer-Company A.xlsm" I run a macro that opens "PriceList.xls" that copys a range from it and paste it in "ClientPriceOffer-Company A.xlsm"

I have the macro under, it works but

- only for the specified file path/name i run the macro from.
-- issue
-- I make a new Excel file for all new clients, then the file name + path changes
-- how to make macro generic, to past values in the book I run the macro from?

- only from the path e:\googleDrive
-- Issue:
-- For other users this path can be different, all compared to where GoogleDrive is located
-- how can the macro open the "PriceList.xls"?
-- what I can do is to place the file in the same structure

-- Price list is here on my computer: e:\Google Drive\Latvija\Price enquiry\PriceList\
---Possible solution ---- > ..\Price enquiry\PriceList\

--ClientPriceOffer-Company A.xlsm is here on my computer e:\Google Drive\Latvija\Price enquiry\"2021-05-12 - ClientPriceOffer-Company A"\"ClientPriceOffer-Company A.xlsm"
---Possible solution ----> ..\Price enquiry\"2021-05-12 - ClientPriceOffer-Company A.xlsm"

Last issue
In sub, after select I3, paste todays date end time: 12.05.2021 08:82 (time can be in cell J3)


==============================
Sub UpdatePriceList()
'
' UpdatePriceList Macro
' Picks up prices from "e:\Google Drive\Latvija\Price enquiry\PriceList\"
'
'
Workbooks.Open Filename:= _
"E:\Google Drive\Latvija\Price enquiry\PriceList\PriceList.xlsx"
Range("B12:I176").Select
Selection.Copy
Windows( _
"ClientPriceOffer-Company A.xlsm" _
).Activate
Range("B6").Select
ActiveSheet.Paste
Range("I3").Select
-----> Here add date
Range("J3").Select
-----> Here add time of day
End Sub


Thanks up front!

 

[TorrO]

Dear mrexcel community, hope all is well in these special times!

I'm struggle to find a generic way to read a path, hope someone can help me one step further