Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    2nd May 2012
    Location
    Canada
    Posts
    109

    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    29th June 2011
    Location
    United States
    Posts
    737

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    2nd May 2012
    Location
    Canada
    Posts
    109

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

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

    VB:
    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
    VB:
    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").

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd May 2012
    Location
    Canada
    Posts
    109

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

    Up

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd May 2012
    Location
    Canada
    Posts
    109

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

    The delete code:

    VB:
    Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).ClearContents 
    
    
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd May 2012
    Location
    Canada
    Posts
    109

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

    Almost have it please Help

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: May 15th, 2007, 02:44
  2. Paste Data To Next Blank Row
    By misspeas in forum EXCEL HELP
    Replies: 1
    Last Post: April 26th, 2007, 22:08
  3. Paste To First Blank Cell In Worksheet
    By workxls in forum EXCEL HELP
    Replies: 25
    Last Post: April 16th, 2007, 23:40
  4. Replies: 8
    Last Post: March 10th, 2007, 03:11
  5. do i search for the blank cell to paste data
    By notEXCELing in forum EXCEL HELP
    Replies: 19
    Last Post: January 7th, 2005, 21:01

Bookmarks

Posting Permissions

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