Range.Copy Destination

30percent

Board Regular
Joined
May 5, 2011
Messages
116
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have the following code. The last part of the codes were to copy a range of data and paste it onto another range on different sheet. I tested every single line of the code up to that point but couldn't figure out why it doesn't work.

Wonder if someone could please help?

Code:
Public finalRow_Paste As Integer
Public finalRow_2_Wks_Trades As Integer


Public Sub Initiate()


    Dim sht_Paste As Worksheet
    Dim sht_2_Wks_Orders As Worksheet
    
    Set sht_Paste = ThisWorkbook.Worksheets("Paste")
    Set sht_2_Wks_Orders = ThisWorkbook.Worksheets("2_Wks_Orders")
    
    finalRow_Paste = sht_Paste.Cells(sht_Paste.Rows.Count, "b").End(xlUp).Row
    finalRow_2_Wks_Orders = sht_2_Wks_Orders.Cells(sht_2_Wks_Orders.Rows.Count, "a").End(xlUp).Row


End Sub




Public Sub Macro1()


Dim days_Elapsed As Integer


ThisWorkbook.Sheets("Paste").Range("1:9").Delete xlUp
Call Initiate


ThisWorkbook.Sheets("Paste").Range(finalRow_Paste + 1 & ":" & finalRow_Paste + 3).Delete xlUp


Worksheets("Hold_Sht").Cells.Clear


Set rngCopy = Worksheets("Paste").Range("A2").Offset(finalRow_Paste, 18)
Set rngCopyHeader = Worksheets("Paste").Range("A1").Offset(finalRow_Paste, 18)
Set rngPaste = Worksheets("Hold_Sht").Range("A2")
Set rngPaste_2 = Worksheets("2_Wks_Orders").Range("A" & finalRow_2_Wks_Orders + 1)




rngCopyHeader.Copy Destination:=rngPaste
rngCopy.Copy Destination:=rngPaste_2
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Have you tried (Just for fun and giggles)

Code:
[COLOR=#333333]Public finalRow_Paste As Integer
[/COLOR]Public finalRow_2_Wks_Trades As Integer


Public Sub Initiate()


    Dim sht_Paste As Worksheet
    Dim sht_2_Wks_Orders As Worksheet
    
    Set sht_Paste = ThisWorkbook.Worksheets("Paste")
    Set sht_2_Wks_Orders = ThisWorkbook.Worksheets("2_Wks_Orders")
    
    finalRow_Paste = sht_Paste.Cells(sht_Paste.Rows.Count, "b").End(xlUp).Row
    finalRow_2_Wks_Orders = sht_2_Wks_Orders.Cells(sht_2_Wks_Orders.Rows.Count, "a").End(xlUp).Row


End Sub




Public Sub Macro1()


Dim days_Elapsed As Integer


ThisWorkbook.Sheets("Paste").Range("1:9").Delete xlUp
Call Initiate


ThisWorkbook.Sheets("Paste").Range(finalRow_Paste + 1 & ":" & finalRow_Paste + 3).Delete xlUp


Worksheets("Hold_Sht").Cells.Clear


Set rngCopy = Worksheets("Paste").Range("A2").Offset(finalRow_Paste, 18)
Set rngCopyHeader = Worksheets("Paste").Range("A1").Offset(finalRow_Paste, 18)
Set rngPaste = Worksheets("Hold_Sht").Range("A2")
Set rngPaste_2 = Worksheets("2_Wks_Orders").Range("A" & finalRow_2_Wks_Orders + 1)




[B][COLOR=#008000]rngCopyHeader.Copy Worksheets("Hold_Sht").Range("A2")
rngCopy.Copy Worksheets("2_Wks_Orders").Range("A" & finalRow_2_Wks_Orders + 1)[/COLOR][/B]
 
Last edited:
Upvote 0
How does the code not work?

Nothing getting pasted? Something getting pasted but in the wrong place? Errors?
 
Upvote 0
How does the code not work?

Nothing getting pasted? Something getting pasted but in the wrong place? Errors?

No error message. nothing getting pasted. As if nothing happened.
Running out of ideas on how to troubleshoot this.

I'm pretty sure when I whipped up the code initially it, 2 line of codes run as expected, copy and paste. But later when I ran the code as whole - it doesn't work, nothing was copied and pasted.
 
Upvote 0
I changed the ThisWorkbook propertyto ActiveWorkbook the thing is ThisWorkbook wont work on excel add ins or if the macro is in a different workbook than were its save ActiveWorkbook can work on a workbook even if the code itself is not in the workbook module give this a try maybe it works.

Code:
Public finalRow_Paste As Integer
Public finalRow_2_Wks_Trades As Integer




Public Sub Initiate()




    Dim sht_Paste As Worksheet
    Dim sht_2_Wks_Orders As Worksheet
    
    Set sht_Paste = ActiveWorkbook.Worksheets("Paste")
    Set sht_2_Wks_Orders = ActiveWorkbook.Worksheets("2_Wks_Orders")
    
    finalRow_Paste = sht_Paste.Cells(sht_Paste.Rows.Count, "b").End(xlUp).Row
    finalRow_2_Wks_Orders = sht_2_Wks_Orders.Cells(sht_2_Wks_Orders.Rows.Count, "a").End(xlUp).Row




End Sub








Public Sub Macro1()




Dim days_Elapsed As Integer




ActiveWorkbook.Sheets("Paste").Range("1:9").Delete xlUp
Call Initiate




ActiveWorkbook.Sheets("Paste").Range(finalRow_Paste + 1 & ":" & finalRow_Paste + 3).Delete xlUp




Worksheets("Hold_Sht").Cells.Clear




Set rngCopy = Worksheets("Paste").Range("A2").Offset(finalRow_Paste, 18)
Set rngCopyHeader = Worksheets("Paste").Range("A1").Offset(finalRow_Paste, 18)
Set rngPaste = Worksheets("Hold_Sht").Range("A2")
Set rngPaste_2 = Worksheets("2_Wks_Orders").Range("A" & finalRow_2_Wks_Orders + 1)








rngCopyHeader.Copy Destination:=rngPaste
rngCopy.Copy Destination:=rngPaste_2
 
Upvote 0
Also just in case I combined the 2 macros into 1

Code:
Sub Macro1()


 Dim finalRow_Paste As Integer
 Dim finalRow_2_Wks_Trades As Integer
 Dim sht_Paste As Worksheet
 Dim sht_2_Wks_Orders As Worksheet
 Dim days_Elapsed As Integer
 Dim rngCopy As Range
 Dim rngCopyHeader As Range
 Dim rngPaste As Range
 Dim rngPaste_2 As Range
 
  Set sht_Paste = ActiveWorkbook.Worksheets("Paste")
  Set sht_2_Wks_Orders = ActiveWorkbook.Worksheets("2_Wks_Orders")


  sht_Paste.Rows("1:9").Delete xlUp
  
  finalRow_Paste = sht_Paste.Cells(sht_Paste.Rows.Count, "b").End(xlUp).Row
  finalRow_2_Wks_Orders = sht_2_Wks_Orders.Cells(sht_2_Wks_Orders.Rows.Count, "a").End(xlUp).Row


  sht_Paste.Range(finalRow_Paste + 1 & ":" & finalRow_Paste + 3).Delete xlUp


  Worksheets("Hold_Sht").Cells.Clear

Set rngCopy = Worksheets("Paste").Range("A2").Offset(finalRow_Paste, 18)
Set rngCopyHeader = Worksheets("Paste").Range("A1").Offset(finalRow_Paste, 18)
Set rngPaste = Worksheets("Hold_Sht").Range("A2")
Set rngPaste_2 = Worksheets("2_Wks_Orders").Range("A" & finalRow_2_Wks_Orders + 1)

rngCopyHeader.Copy Destination:=rngPaste
rngCopy.Copy Destination:=rngPaste_2


End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top