Copy & Paste to different Excel workbooks while retaining formats

I’ve tried to manually copy & paste manually (ie. Ctrl C and Ctrl V), with Excel visible, as format & formulas need to be retained between different workbooks. This is currently working most of the time. The issue comes in when the it’s not working sometimes.

As it is performing for quite some time for quite a number of files (perhaps half an hour or so), it may experience some issues:
Issue 1) Exception from HRESULT: 0x800AC472
Issue 2) Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))

Are there any ways to solve this? It should be reliable for all the files.

1 Like

Fine
if we are trying to copy the entire file then we can use COPY FILE activity

or are we trying to copy a selected range
Cheers @Nisa

Hey @Palaniyappan!

Unfortunately, I’m trying to copy a selected range, copy file wouldn’t work in this case

1 Like

no worries
we can use SELECT RANGE and GET SELECTED RANGE activity in this case


and get the output with variable having that range stored which can be used in write range activity

the above mentioned link has some example as well
hope this would help you
kindly try this and let know for any queries or clarification
Cheers @Nisa

Hey @Palaniyappan,

Get Selected Range outputs a string variable with the range as a string. How can that be used after getting the range?

And sorry, perhaps my question was not clear at the start. To simplify my question, is there a way to copy the data (with formula and formats) to another workbook without using hotkeys? I need to ensure stability for the program.

Good evening Nisa,

I’m not sure how familiar you are with VBA, this sounds like something you could pretty easily accomplish with a quick subroutine.

This solution may also work for you:

Hi @chenderson!

With regards to the second option (ie. Ctrl C and Ctrl V), I’ve been doing that actually. However, as it needs to do for multiple (about 50) Excel files and Macro files (to be specific, .xls, .xlsx, and xlsm), there have been errors occasionally, as mentioned.

For the VBA option, I’m not very familiar with VBA. However, I tried this with invoke VBA, which works for .xls or .xlsx files:

Sub CopyPaste(ByVal AFileName As String, ByVal ATabName As String, ByVal ARange As String, ByVal BFileName As String, ByVal BTabName As String, ByVal BRange As String)

  Workbooks(AFileName).Worksheets(ATabName).Range(ARange).Copy
  Workbooks(BFileName).Worksheets(BTabName).Range(BRange).PasteSpecial Paste:=xlPasteAll

End Sub

But when i invoke VBA with an .xlsm file as a file name, it gives the following error:

Invoke VBA: Cannot run the macro 'CopyPaste'. The macro may not be available in this workbook or all macros may be disabled.

Are you familiar with VBA yourself? Any advice on how to get it to work on .xlsm without changing the current code for .xlsm? Thanks in advance.

bump

any responses on this? I’m still trying to find a solution without using a ctrl c and ctrl v, and preferably not opening Excel visibly.

Main issue is copying for xlsm file, whereby it’s giving this error while I’m performing a sub:

Invoke VBA: Cannot run the macro 'CopyPaste'. The macro may not be available in this workbook or all macros may be disabled.