Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    13
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Paste an array to another with beginning the next empty range

    Hi all,
    I have a part of my code given below, which i use to paste a range into a given array below the last non-blank row.
    But i need this code to paste the selected range to the next non-blank row, because now i sort the target array and there is a gap between pasted rows.
    I also attached the file to this message, and here's the operation i need to be done;

    Currently if i run the code, it will paste the range to row Nr. 13
    I need to paste it to the row Nr. 8

    I'd appreciate if you can help me modifying this code and learn about it.


    Code:
    Private Sub CommandButton1_Click()
    
    
        Application.ScreenUpdating = False
        Dim copySheet As Worksheet
        Dim pasteSheet As Worksheet
    
    
        'to check if any inconsistency
        If Range("S12") = "No" Then
            UserForm2.Show
            Exit Sub
        End If
        If Range("S15") = 0 Then
            UserForm3.Show
            Exit Sub
            If Range("S16") = 0 Then
                UserForm3.Show
                Exit Sub
                If Range("S17") = 0 Then
                    UserForm3.Show
                    Exit Sub
                End If
            End If
        End If
        Set copySheet = Worksheets("Schedule")
        Set pasteSheet = Worksheets("Schedule")
        copySheet.Range("Q5:AC5").Copy
        pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        'to produce a new id
        Range("Q5").Value = Range("Q5").Value + 1
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    
    
    End Sub
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi lerq

    try this:
    Code:
    Private Sub CommandButton1_Click()
    
    
        Application.ScreenUpdating = False
        Dim copySheet As Worksheet
        Dim pasteSheet As Worksheet
    
    
        'to check if any inconsistency
        If Range("S12") = "No" Then
            UserForm2.Show
            Exit Sub
        End If
        If Range("S15") = 0 Then
            UserForm3.Show
            Exit Sub
            If Range("S16") = 0 Then
                UserForm3.Show
                Exit Sub
                If Range("S17") = 0 Then
                    UserForm3.Show
                    Exit Sub
                End If
            End If
        End If
        Set copySheet = Worksheets("Schedule")
        Set pasteSheet = Worksheets("Schedule")
        copySheet.Range("P5:AC5").Copy
        pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        'to produce a new id
        [P5]= [P5].Value + 1
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    
    
    End Sub
    Your code in post#1 is copying and pasting on the same sheet.
    In your attached file, the data is is in [P5:AC5], not [Q5:AC5]

    zeddy

Posting Permissions

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