Copy/Paste to next blank row...if

[Ragram46]

I have a macro that copies/pastes to next row-below, but what I am trying to accomplish is that in the event some data was deleted above last row pasted...it would know to post in that blank row above, then continue as normal pasting below.

Sub Data()
'Modified 12/5/2018 3:21:41 PM EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row + 1
If Lastrow < 12 Then Lastrow = 12
Range("C6:G6").Copy
Cells(Lastrow, "C").PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Joe4]

If you want to find the first blank row, even if it is in the middle of your data, you can work from the top down instead of the bottom up.
Change your "Lastrow" calculation to this:

[COLOR=#333333]Lastrow = Cells(1, "C").End(xlDown).Row + 1[/COLOR]

 

[Ragram46]

This is part of my many struggles...when you say "change your 'last row' calculation" where does that substitution take place. Like, whats removed and where does this insert into.

Thanks-

 

[Joe4]

This is part of my many struggles...when you say "change your 'last row' calculation" where does that substitution take place. Like, whats removed and where does this insert into.

I take it that you did not write the code that you are using then.
Replace the line in red below with the line I posted above in my previous post:

[COLOR=#333333]Sub Data()[/COLOR]
[COLOR=#333333]'Modified 12/5/2018 3:21:41 PM EST[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]
[COLOR=#333333]Dim Lastrow As Long[/COLOR]
[COLOR=#ff0000]Lastrow = Cells(Rows.Count, "C").End(xlUp).Row + 1[/COLOR]
[COLOR=#333333]If Lastrow < 12 Then Lastrow = 12[/COLOR]
[COLOR=#333333]Range("C6:G6").Copy[/COLOR]
[COLOR=#333333]Cells(Lastrow, "C").PasteSpecial xlValues[/COLOR]
[COLOR=#333333]Application.CutCopyMode = False[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = True[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Ragram46]

Super Excel Amateur here...of course i didnt "write" the code. I copied/tweaked. This is difficult stuff.

Thanks for your help!

 

[Ragram46]

i did the substitution...and now the macro will only paste to 1 line. Where as before I could keep posting row after row. I click it 3 times and it only pastes it to row C12...i wanted it to index to C13 next and so on and then after I paste to C20 if i delete C14 for an error...the next paste will go to C14 then C21

 

[Joe4]

I was just answering your specific question about the existing code. You haven't described what your current data structure is, and exactly how you intend to use this (and what you want it do exactly).
Without those details, I cannot really make a good suggestion.

 

[Ragram46]

We have a living document that we use to build estimates/quotes. Instead of manually entering everything (time consuming/inconsistent entries) i wanted to standardize the process. I have two worksheets. Sheet1 is the quote form with drop down menus to select the Vendor/System/Part/Finish. Then a VLookup finds the selected product on Sheet2 and returns the line of data. Once the user enters in quantity they can click a button to paste that line of data into the form below it on Sheet1. I have all the data on its own sheet and the quoet form/fill out section is its own sheet. If by chance we make a mistake on an entry we can just delete that rows information...then when we make the correct selection I'd like it to paste into the first open void, if C12 has information then it will automatically paste to C13 and so forth.

 

[Joe4]

Try this:

Sub DataCopy()
    
    Application.ScreenUpdating = False
    
    Dim Lastrow As Long
    
    If Range("C12") = "" Then
        Lastrow = 12
    Else
        If Range("C13") = "" Then
            Lastrow = 13
        Else
            Lastrow = Cells(12, "C").End(xlDown).Row + 1
        End If
    End If
    
    Range("C6:G6").Copy
    Cells(Lastrow, "C").PasteSpecial xlValues
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    
End Sub

 

[Ragram46]

Unbelievable. I cannot fathom how you did this...it works !!!