Find next blank line then paste data

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
Hi,
On the Service Order tab (Link below) there is a button that runs a macro to extract certain data form the Service Order form then paste to the imported Data sheet.
How can I continue to add different but new data below the last pasted entry in the Imported Data sheet? Code to find next blank row?
Regards,
Wayne


VOID VALIDATION KC.xlsm
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,068
Office Version
365, 2010
Platform
Windows, Mobile
Maybe the below. Just a pointer but you will get more people looking and therefore responding (+ faster) if you post your code in the thread rather than making them download a workbook (a lot of posters won't even download workbooks because of security reasons).
To post your code in the thread all you need to do is copy the code, paste it in the thread then select the code and click the # icon (the last steps put it in code tags).

Code:
Sub Import_SOR_Data1()
    '
    ' Import_SOR_Data Macro
    st = Timer
    Set ws1 = Sheets("ServiceOrder")
    Set ws2 = Sheets("Imported data")
    
    ws2.Range("A1").Value = ws1.Range("BL3").Value
    ws2.Range("A2").Value = ws1.Range("R16").Value
    
    Application.ScreenUpdating = False
    

    LastRow = ws1.Cells(40, "A").End(xlDown).Row
    lastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp)(2).Row
    
    With ws1
        .Range("a40:a" & LastRow).Copy ws2.Cells(lastrow2, "a")
    
        .Range("p40:p" & LastRow).Copy ws2.Cells(lastrow2, "b")
    
        .Range("ac40:ac" & LastRow).Copy ws2.Cells(lastrow2, "c")
    
        .Range("al40:al" & LastRow).Copy ws2.Cells(lastrow2, "d")
    
        .Range("bk40:bk" & LastRow).Copy ws2.Cells(lastrow2, "e")
       
    End With
    
    For i = 1 To 5
        nr = Choose(i, 11, 11, 8, 55, 20)
        ws2.Columns(i).ColumnWidth = nr
    Next i
    
    ws2.Range("A3").CurrentRegion.Rows.AutoFit

    ws2.Cells.Borders.LineStyle = xlLineStyleNone
    Application.ScreenUpdating = True
    Debug.Print Timer - st                       '0.18 sec
End Sub
 
Last edited:

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
THANK you for that, I was wondering why no reply to many questions? I had been talking with one helper and he gt cranky and would only reply if i attached workbooks. I have posted code before and had good responses.
The code has since changes a little trying to capture data from a merged cell format and found 2 lots of code did not copy as each had a column changed but the form appeared the same.
Here is the update;

Code:
Sub Import_SOR_Data2()
'
' Import_SOR_Data Macro
    st = Timer
    Set ws1 = Sheets("ServiceOrder")
    Set ws2 = Sheets("Imported data")
    
    'ws2.Range("A1").Value = ws1.Range("BL3").Value
    rownr = Cells.Find("Service Order").Row
    colnr = Cells.Find("Service Order").Column
    Cells(rownr + 0, colnr).End(xlToRight).Select

    ws2.Range("A2").Value = ws1.Range("R16").Value
    
    ws1.Select
    lastrow = Cells(40, "A").End(xlDown).Row
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Trade").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period

    'Range("a40:a" & lastrow).Copy
    ws2.Cells(3, "a").PasteSpecial
    
    'Range("p40:p" & lastrow).Copy
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Item Code").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "b").PasteSpecial
    
    'Range("ac40:ac" & lastrow).Copy
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Qty/Hrs").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "c").PasteSpecial
    
    'ws2.Cells(3, "c").PasteSpecial
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Description").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "d").PasteSpecial
    'Range("al40:al" & lastrow).Copy
    
    'ws2.Cells(3, "d").PasteSpecial
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Location/Asset").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "e").PasteSpecial
    'Range("bk40:bk" & lastrow).Copy
    
    'ws2.Cells(3, "e").PasteSpecial
       
        
    For i = 1 To 5
        nr = Choose(i, 11, 11, 8, 55, 23)
        ws2.Columns(i).ColumnWidth = nr
    Next i
    
    ws2.Range("A3").CurrentRegion.Rows.AutoFit
    ws2.Select
    Cells(1, 1).Select
    Cells.Borders.LineStyle = xlLineStyleNone
    Debug.Print Timer - st                                            '0.18 sec
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,068
Office Version
365, 2010
Platform
Windows, Mobile
trying to capture data from a merged cell format
I am afraid that I don't touch anything to do with merged cells so can't help you with that.
 

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
Its only the copying to the Imported Data sheet that I would like to know how to set the CopyPaste to be below the last row of existing data.
No question on merged cell copy really. That's another thread.
Regards,
Wayne
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,068
Office Version
365, 2010
Platform
Windows, Mobile
Its only the copying to the Imported Data sheet that I would like to know how to set the CopyPaste to be below the last row of existing data.
The code posted in post number 2 already shows you how to do that.
 

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
My bad.
I posted the changed code from the one you posted. So the comparison was not obvious.
I want to apply it to the code I posted if at all possible?
Regards,
Wayne
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,068
Office Version
365, 2010
Platform
Windows, Mobile
Your syntax here from post number 3

Code:
    colnr = Cells.Find("Location/Asset").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    [COLOR="#FF0000"]ws2.Cells(3, "e").PasteSpecial[/COLOR]
is the same as in your spreadsheet in post number 1 here

Code:
    Range("bk40:bk" & lastrow).Copy
    [COLOR="#FF0000"]ws2.Cells(3, "e").PasteSpecial[/COLOR]
and so can be handled in exactly the same way as the code in post number 2

Code:
 [COLOR="#FF0000"]lastrow2 = ws2.Cells(Rows.Count, "A").End(xlUp)(2).Row[/COLOR]
    
    With ws1
        .Range("a40:a" & LastRow).Copy ws2.Cells(lastrow2, "a")
    
        .Range("p40:p" & LastRow).Copy ws2.Cells(lastrow2, "b")
    
        .Range("ac40:ac" & LastRow).Copy ws2.Cells(lastrow2, "c")
    
        .Range("al40:al" & LastRow).Copy ws2.Cells(lastrow2, "d")
    
        .Range("bk40:bk" & LastRow).Copy [COLOR="#FF0000"]ws2.Cells(lastrow2, "e")[/COLOR]
       
    End With
 
Last edited:

tynawg

New Member
Joined
Oct 11, 2019
Messages
42
Hi MARK858,

I can see the process now, thank you.
I have however had to change the code a little since then and wonder if you might have time to assist again?
Also the entire range needs to copy to the next blank ws2, which includes BL2, BL3 and R16?
Regards,
Wayne

Code:
Sub Import_SOR_Data2()
'
' Import_SOR_Data Macro
    st = Timer
    Application.ScreenUpdating = False
    Dim celA As Range, celB As Range, celC As Range
    Set ws1 = Sheets("ServiceOrder")
    Set ws2 = Sheets("Imported data")
    Set cel = ws2.Range("A1")
    Set celA = ws1.Range("BL3")
    Set celB = ws1.Range("BK3")
 
    If IsNumeric(celA) And celA <> "" Then cel = celA Else cel = celB
    ws2.Range("A2").Value = ws1.Range("R16").Value
    
    ws1.Select
    lastrow = Cells(40, "A").End(xlDown).Row
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Trade").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period

    ws2.Cells(3, "a").PasteSpecial
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Item Code").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "b").PasteSpecial
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Qty/Hrs").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "c").PasteSpecial
    
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Description").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "d").PasteSpecial
   
    colnr = 0 'set this to zero everytime
    colnr = Cells.Find("Location/Asset").Column 'search term must be exact
    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy   '3 commas, 1 period
    ws2.Cells(3, "e").PasteSpecial
        
    For i = 1 To 5
        nr = Choose(i, 11, 11, 8, 55, 23)
        ws2.Columns(i).ColumnWidth = nr
    Next i
    
    ws2.Range("A3").CurrentRegion.Rows.AutoFit
    ws2.Select
    Cells(1, 1).Select
    Cells.Borders.LineStyle = xlLineStyleNone
    Application.ScreenUpdating = True
    Debug.Print Timer - st                                            '0.18 sec
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,068
Office Version
365, 2010
Platform
Windows, Mobile
Post number 8 shows you how to post to the next row.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,742
Messages
5,410,169
Members
403,302
Latest member
Richard Genet

This Week's Hot Topics

Top