Find next blank line then paste data

[tynawg]

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

 

[tynawg]

Thanks Mark858,
It does not address the boolean or colnr but thank you for taking the time to reply.
Regards,
Wayne

 

[MARK858]

What Boolean? colnr just replaces the column letter.

 

[tynawg]

Hi Mark858,

At the moment If IsNumeric line is looking for the cell that has the text and it is also hard coded to A1, then R16 to A2, then the range is below those 2.

I don't understand where or how to incorporate your suggestions, sorry. Otherwise I probably wouldn't need to post here.
Regards,
Wayne

    Dim celA As Range, celB As Range, cel 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

 

[MARK858]

I am afraid at the moment I am not understanding what you are trying to do.
Please explain in words (without referring to the code that isn't doing what you want) what you are trying to do.

 

[tynawg]

So, I have a situation where client sends work orders in a merged cell format. I am trying to create a macro to extract certain cells and a range of cells and copy them below the last work order on the Import data sheet. (Have not figured out how to import that merged data to run macro on yet)
A1 is the work order number in ws2.
A2 is the job address in ws2..
A3 is the range of Item codes issued to this work order in ws2.
I want to extract 3 sets of data in that sequence and copy/paste to next blank row. Then I will advance to my next question.
However recently I found the WO changes sometimes and the data that was in cell BL3 changed to BK3 hence trying to find which cell has the work order number in it to then copy.
Regards,
Wayne

 

[tynawg]

This is the post to how to import the data to run a macro on.
Regards,
Wayne

https://www.mrexcel.com/forum/excel...t-new-data-into-existing-sheet-run-macro.html

 

[MARK858]

Must be me tonight but none the wiser so rather than use a lastrow variable I will try and show how to post to the next blank row on an individual column so....

For column E...

    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy ws2.Cells(Rows.count, "[COLOR="#FF0000"][B]E[/B][/COLOR]").End(xlUp)(2)

or for column B

    Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy ws2.Cells(Rows.count, "[COLOR="#FF0000"][B]B[/B][/COLOR]").End(xlUp)(2)

If it makes more sense to you then you can replace the (2) with .Offset(1, 0) i.e.

Range(Cells(40, colnr), Cells(lastrow, colnr)).Copy ws2.Cells(Rows.count, "B").End(xlUp)[COLOR="#FF0000"][B].Offset(1, 0)[/B][/COLOR]

 

[tynawg]

Perhaps if I post a link to the workbook it might be easier to see what I am on about or trying to do?