Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Range.Copy Destination

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Range.Copy Destination

    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

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Posts
    332
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Range.Copy Destination

    Have you tried (Just for fun and giggles)

    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 Worksheets("Hold_Sht").Range("A2")
    rngCopy.Copy Worksheets("2_Wks_Orders").Range("A" & finalRow_2_Wks_Orders + 1)
    Last edited by Truiz; Nov 8th, 2017 at 05:18 PM.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,090
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Range.Copy Destination

    How does the code not work?

    Nothing getting pasted? Something getting pasted but in the wrong place? Errors?
    If posting code please use code tags.

  4. #4
    Board Regular
    Join Date
    May 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Range.Copy Destination

    Tried the alternative as suggested. Same result.

  5. #5
    Board Regular
    Join Date
    May 2011
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Range.Copy Destination

    Quote Originally Posted by Norie View Post
    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.

  6. #6
    Board Regular
    Join Date
    Jul 2014
    Posts
    332
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Range.Copy Destination

    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

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Jul 2014
    Posts
    332
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Range.Copy Destination

    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 by Truiz; Nov 9th, 2017 at 10:14 AM.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •