0

I'm simply trying to copy and paste between workbooks in VBA without first activating each workbook that I want to perform actions on.

My code stores the active workbook and sheet in variables before creating a new workbook after which cell A1 from the original workbook is supposed to be copied to the newly created one.

Option Explicit

Sub test()

Dim pLWB As Workbook
Dim pLWS As Worksheet

Set pLWB = ActiveWorkbook
Set pLWS = ActiveSheet

Workbooks.Add

pLWB.pLWS.range("A1").copy

End Sub

Any ideas where I am going wrong? I was under the impression that you can refer to very specific cells through workbooks.worksheets.range but even as I was typing, the intellisense menu did not show pLWS as an option.

Thank you in advance for your time.

  • You need to open the source WB – Raystafarian Aug 20 '15 at 11:31
  • @Raystafarian Do you mean open or active? Both the workbooks are open. – M.Hesse Aug 20 '15 at 16:50
  • Well that's not included in your code. I have no idea what the variables should be or what is active – Raystafarian Aug 20 '15 at 17:35
  • @Raystafarian Apologies, I already have one workbook open which is active. This active workbook and sheet is stored in the pL variables after which a new workbook is created. – M.Hesse Aug 20 '15 at 17:38
  • Please include all of the code – Raystafarian Aug 20 '15 at 17:38
1

You need to refer to the sheet differently - it's just one of those things.

Option Explicit

Sub test()

Dim pLWB As Workbook
Dim pLWS As Worksheet

Set pLWB = ActiveWorkbook
Set pLWS = pLWB.ActiveSheet

Workbooks.Add

pLWS.Range("A1").Copy

End Sub

Also this works -

Option Explicit

Sub test()

Dim pLWB As Workbook
Dim pLWS As String

Set pLWB = ActiveWorkbook
 pLWS = ActiveSheet.Name

'Workbooks.Add

pLWB.Sheets(pLWS).Range("A1").Copy

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.