Range.Copy Destination

[30percent]

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?

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

 

[Truiz]

Have you tried (Just for fun and giggles)

[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]

 

[Norie]

How does the code not work?

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

 

[30percent]

Tried the alternative as suggested. Same result.

 

[30percent]

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.

 

[Truiz]

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.

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

 

[Truiz]

Also just in case I combined the 2 macros into 1

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