How do I record a macro that involves two workbooks?

Question

I've tried using the record macro icon, but when I checked the macro, it recorded only my movements in the current workbook.

So is it possible to run a macro in the current workbook that would copy from a previous worksheet and paste on the current worksheet?

Because I intend to use that macro in the future, to copy as picture the sparkline graphs of hundreds of data, as it would be too tedious to do that, where I would have to click each sparkline graph to copy it as picture to the new workbook.

Thanks.

Answer 1

ok,

1) Selection as picture in new WB

Sub Selection_AsPicture_NewWB()

'July 10, 2015

Const cel As String = "A1" '<< top left cell

If MsgBox("select a range ?", vbOKCancel) = vbCancel Then Exit Sub

Selection.CopyPicture xlScreen, xlBitmap

Set wb = Workbooks.Add(xlWorksheet)

With ActiveSheet

.Paste Destination:=.Range(cel)

.Range(cel).Select

End With

'wb.Save

End Sub

xxxxxxxxxxxxxxxxxxxxxxxxxxxx

2) Selection as picture in new Worksheet

Sub Selection_AsPicture_NewWS()

'July 10, 2015

Const cel As String = "A1" '<< top left cell

If MsgBox("select a range ?", vbOKCancel) = vbCancel Then Exit Sub

Selection.CopyPicture xlScreen, xlBitmap

Set ws = Sheets.Add(before:=Sheets(1))

With ActiveSheet

.Paste Destination:=.Range(cel)

.Range(cel).Select

End With

ThisWorkbook.Save

End Sub

Answer 2

I was referring to 2 workbooks, and I just edited my question.

Answer 3

Do you mean to say - the macro must exist in one workbook and executed on other workbooks ?

Are you referring to two worksheets or two workbooks ?

It is better for you to be aware of basic VBA programming language.

Record macro option helps you to generate a macro code based on the actions/steps which you as a user execute and not beyond that.

Knowing VBA coding could be necessary for writing more complex macros.

Hope this is Helpful.

Answer 4

I'm sorry, what I meant is a new workbook.

Answer 5

Hi,

try this sample

select a range from Activesheet  and paste (as picture) in sheet2  (2nd sheet tab)

(target range, top left cell is B2)

Sub CopyPaste_AsPicture()

'July 10, 2015

Const cel As String = "B2" '<< target cell, in 2nd sheet tab

If MsgBox("select a range ?", vbOKCancel) = vbCancel Then Exit Sub

Dim ws As Worksheet

Set ws = Sheets(2) '<< 2nd sht tab

Selection.CopyPicture xlScreen, xlBitmap

ActiveSheet .Paste Destination:=ws.Range(cel)

End Sub