Macro to copy paste without having all the source books opened [Solved/Closed]

Report
Posts
2
Registration date
Wednesday October 8, 2014
Status
Member
Last seen
October 12, 2014
-
Posts
2
Registration date
Wednesday October 8, 2014
Status
Member
Last seen
October 12, 2014
-
Hello Kioskea users. I hope you're all doing great. Today I have a concern that I'm not able to figure out. Any help will be really appreciated.

I'm creating a macro in excel to copy and paste the values from one workbook into another one. So far it works like a charm but it only works when I have all the files open. How can I make the macro get the values without having the other files opened? So far I have this:

Sub PastefromDashboard()
'
' test Macro
'

'Copy Values from Source
Windows("Dashboard.xlsm").Activate
Range("F151:L160").Select
Selection.Copy

Windows("Destination").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

I appreciate your time and any help that can provide me with.

Thanks,
Fabian

2 replies

Posts
2660
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 29, 2020
442
Hi Fabian,

To open a workbook use (alter the file path to match yours):
Workbooks.Open "C:\Documents\Dashboard.xlsm"

To close the workbook and save the changes without being prompted, use:
Workbooks("Dashboard.xlsm").Close SaveChanges:=True

Best regards,
Trowa
Posts
2
Registration date
Wednesday October 8, 2014
Status
Member
Last seen
October 12, 2014

Hello TrowaD! You have no idea of how much I appreciate your help! It's working! It's amazing!

Thanks again,
Fabian