Announcement

Collapse
No announcement yet.

VBA To Paste Data Into Next Blank Cell On Another Worksheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA To Paste Data Into Next Blank Cell On Another Worksheet



    Excel 2003
    I would like to use a code on one worksheet to initiate copy data from that worksheet and paste the data onto another worksheet in the same workbook. As this may be used multiple times, the data should be pasted into the next empty available row.

    The user will have information on Sheet5 from row A to P. Doesn't matter the Column.
    I have the following code but need to modify it to do the following:

    Code:
    If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
    If Target.Cells.Count = 1 Then ' stops the code looping
    If LCase(Target.Value) = "yes" Then
    Range("A5:P5").Copy
    Sheets("Archives").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlvalue
    End If
    Target.EntireRow.Delete 'I need this to be clear content instead of delete the entire row
    End If
    End If

    I do not need to have A5:P5. I need the selected row to copy the info from A:P that the user selected from Row? to the next available row in my Archives sheet.

    I need that everytime a user select a row and from the Q column he selects "yes" then to have that row copy to the next available row in my Archives sheet and then clear the content that was in that selected row.

    Ex: Q5 Is selected and the user select "yes", then Copy and paste to next available row in Sheet("Archives") then Clear content in Sheet5 A5:P5
    Ex: Q9 Is selected and the user select "yes", then Copy and paste to next available row in Sheet("Archives") then Clear content in Sheet5 A9:A9
    Ex: Q8 Is selected and the user select "yes", then Copy and paste to next available row in Sheet("Archives") then Clear content in Sheet5 A8:A8

    If we take this example my sheet"Archives" will have the following info from the Sheet5 instruction:

    A5:P5 = Info copied from Sheet5 A5:P5
    A6:P6 = Info copied from Sheet5 A9:P9
    A7:P7 = Info copied from Sheet5 A7:P7

    Please Help

  • #2
    Re: VBA To Paste Data Into Next Blank Cell On Another Worksheet

    You can use ClearContents to clear the content of cells
    Code:
    Range("A1").ClearContents

    Comment


    • #3
      Re: VBA To Paste Data Into Next Blank Cell On Another Worksheet

      Code:
      Range("A1").ClearContents
      To replace the

      Code:
      Target.EntireRow.Delete
      Yes but as I explained in details I need also the code to copy what is in the row selected got to the next available row on my Sheet("Archives"). If I only change to ClearContents then it's only clearing A1 if I select my
      Code:
      Range("Q:Q")
      If you look at my example or even my code, this only takes A5:P5 but if I go on row 10 and select Cell Q and insert "yes" this will only delete/clear the content and the copy will not be made.

      I need that everytime the user selects a row vs the Cell Q and insert "yes" to have range A:P transfered to the next available row in Sheet("Archives").

      Comment


      • #4
        Re: VBA To Paste Data Into Next Blank Cell On Another Worksheet

        Up

        Comment


        • #5
          Re: VBA To Paste Data Into Next Blank Cell On Another Worksheet

          The delete code:

          Code:
          Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).ClearContents
          Code:
          If Not Intersect(Target, Range("Q:Q")) Is Nothing Then ' Everytime the use selects Cell Q any row
          If Target.Cells.Count = 1 Then ' stops the code looping
          If LCase(Target.Value) = "yes" Then ' yes will be in a validation list so that the user will be able to select it
          Range("A5:P5").Copy ' I need this to be from Range("A:P") but it brings up an error
          Sheets("Archives").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValue ' this will copy the info that was on the row selected then paste it on a permanent basis to Sheets("Archives")
          End If
          Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).ClearContents ' This will clear the data on the selected row that was selected and clear the content from Cell A to P
          End If
          End If

          Comment


          • #6


            Re: VBA To Paste Data Into Next Blank Cell On Another Worksheet

            Almost have it please Help

            Code:
            If Not Intersect(Target, Range("Q:Q")) Is Nothing Then
                If Target.Cells.Count = 1 Then ' stops the code looping
                If LCase(Target.Value) = "yes" Then
                   Range(Cells(Target.Row, 1), Cells(Target.Row, 16)).Copy
                   Else
                  Sheets("Archives").Cells(Last(1, Sheets("Archives").UsedRange) + 1, "A").PasteSpecial xlPasteValues
            End If
                   Range(Cells(Target.Row, 1), Cells(Target.Row, 17)).ClearContents
                End If
            End If
            But needs to be next available row

            Comment

            Working...
            X