Hello all,
I am trying to write a VBA macro to assist me in copying few Named cells from one workbook to another.
Source workbook: "WBSource"
Destination workbook: "WBDest" --- Destination workbook to hold the Macro
I want to be able to go to:
1. I manually open "WBDest"
2. Run a macro "Macro"
3. Macro asks me for the path of "WBSource", and open it
4. Macro copy the values of two named ranges "NamedRange1" and "NamedRange2" from WBSource
5. Macro go back to WBDest, open a specific sheet "TargetSheet" and paste (values only) NamedRange1 to Cell D9 and NamedRange2 to D10 of the "TargetSheet" in "WBDest"
Looking around in the internet, I was able to put together the below code that copies 1 cell from WBSource to WBDest. I am looking for assistance to modify the below code to do what's described above.
Thank you in advance
I am trying to write a VBA macro to assist me in copying few Named cells from one workbook to another.
Source workbook: "WBSource"
Destination workbook: "WBDest" --- Destination workbook to hold the Macro
I want to be able to go to:
1. I manually open "WBDest"
2. Run a macro "Macro"
3. Macro asks me for the path of "WBSource", and open it
4. Macro copy the values of two named ranges "NamedRange1" and "NamedRange2" from WBSource
5. Macro go back to WBDest, open a specific sheet "TargetSheet" and paste (values only) NamedRange1 to Cell D9 and NamedRange2 to D10 of the "TargetSheet" in "WBDest"
Looking around in the internet, I was able to put together the below code that copies 1 cell from WBSource to WBDest. I am looking for assistance to modify the below code to do what's described above.
Thank you in advance
VBA Code:
Sub Macro()
Dim WBSourcePath As String: WBSourcePath = Application.GetOpenFilename(, , "Locate the WBSource") 'Step [A]: Grabs the WBsource file path
Set WBSource = Workbooks.Open(Filename:=WBSourcePath) 'Define WBSource as path from Step [A]
Set WBDest = ThisWorkbook
WBSource.Worksheets("Input").Select
Range("A20").Copy 'Copy the A20 from Sheet "Input" WBSource info
WBDest.Worksheets("TargetSheet").Activate
Range("D9").PasteSpecial Paste:=xlPasteValues 'Paste values into WBDest Sheet TargetSheet Cell D9
Application.CutCopyMode = False 'Forbids Macro from cutting information from Active workbook, the active selection tool
MsgBox ("Done")
End Sub