Answered by:
VBA Excel - Copy Paste Array

Question
-
Hi friends,
I am trying to write a copy paste array.
I get an error object worksheet not found
Here is my code
Sub Copy_Paste_Array() Dim i As Long Dim ows As Excel.Worksheet Dim oSWksht As Excel.Worksheet Dim oDWksht As Excel.Worksheet oSWksht = ActiveWorkbook.Worksheets("AA") oDWksht = ActiveWorkbook.Worksheets("BB") oCopyRange = Array("A1", "A2") oDestinationRange = Array("A1", "A2") For i = LBound(oCopyRange) To UBound(oCopyRange) oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i).Value) Next i End Sub
Please may some one look at it
Thank you for your time
Cheers Dan :)
Tuesday, November 7, 2017 5:19 PM
Answers
-
Since worksheets are objects, you have to use the keyword Set to assign a value to a variable of type Worksheet:
Set oSWksht = ActiveWorkbook.Worksheets("AA")
Set oDWksht = ActiveWorkbook.Worksheets("BB")Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
Tuesday, November 7, 2017 5:50 PM -
Sorry about that, it should have been
oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i))
Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
Tuesday, November 7, 2017 9:42 PM
All replies
-
Since worksheets are objects, you have to use the keyword Set to assign a value to a variable of type Worksheet:
Set oSWksht = ActiveWorkbook.Worksheets("AA")
Set oDWksht = ActiveWorkbook.Worksheets("BB")Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
Tuesday, November 7, 2017 5:50 PM -
Hello Hans,
I fixed that thank you.
I still get object required on
oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i).Value)
:(
Cheers Dan :)
Tuesday, November 7, 2017 6:41 PM -
Sorry about that, it should have been
oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i))
Regards, Hans Vogelaar (http://www.eileenslounge.com)
- Marked as answer by Dan_CS Tuesday, November 7, 2017 10:25 PM
Tuesday, November 7, 2017 9:42 PM -
Hello Hans,
thank you for your kind help.
Sub Copy_Paste_Array() Dim oSWksht As Worksheet Dim oDWksht As Worksheet Dim i As Long Set oSWksht = ActiveWorkbook.Worksheets("AA") Set oDWksht = ActiveWorkbook.Worksheets("BB") oCopyRange = Array("A1", "A2") oDestinationRange = Array("A30", "A20") '---------------------------------------- For i = LBound(oCopyRange) To UBound(oCopyRange) oSWksht.Range(oCopyRange(i)).Copy Destination:=oDWksht.Range(oDestinationRange(i)) Next i End Sub
Here is the fixed code working nicely :)
Cheers Dan :)
- Edited by Dan_CS Tuesday, November 7, 2017 10:26 PM
Tuesday, November 7, 2017 10:22 PM -
Hello Hans,
It copied it nicely , sorry my second array term was empty doh!
Its been a long day
Thank you as always
Cheers Dan :)
Tuesday, November 7, 2017 10:25 PM -
The code operates as intended when I run it. Make sure that A1 and A2 on sheet AA contain a value...
Regards, Hans Vogelaar (http://www.eileenslounge.com)
Tuesday, November 7, 2017 10:28 PM