VBA copy to another tab, and then go to the next available blank cell

[Cele]

Hi,

Was wondering if you can help me fix this maro. The one called Copy to List

I need it to do the following..


Copy the data on column H and N only.

And once it does, to go the next available blank cell on column H.

So when I press the macro button again, it copies on the next available cell on column H and so on. Can you help.



Cele

 

[PCosta87]

Hi,

Something like this should work:

Sub CopyToList()

    Dim frow, lrow As Integer

    Selection.End(xlDown).Activate
    frow = Selection.Row
 
    Selection.End(xlDown).Activate
    lrow = Selection.Row
 
    Range("H" & frow & ":H" & lrow & ", N" & frow & ":N" & lrow).Copy
 
    Range("H" & lrow + 1).Activate
 
End Sub

Select an empty cell in H above what you want to copy and then run the code
It will copy to clipboard so you can paste wherever you want.

Just a warning, if pressed by accident without any data below, it will throw an error... you should probably account for that too.

Hope this helps

 

[Cele]

Hmm..unfortunately came up with the error regardless.

 

[PCosta87]

Hmm..unfortunately came up with the error regardless.

Hi,

The code will go down from the active cell to the first non empty one and copy all data up to the next empty cell.
For it to work you need to have selected an empty cell in column H, above what you want to copy, something like "H3"
Then, just press the button to run the code and it will select the first blank cell after the copied selection.

Do it like this:
1) Upon opening the file, click the button I just added. Notice that it copies the data from H & N as requested:


Try pasting it somewhere else to confirm that it copied correctly.

Please refer to attachment.

 

[Cele]

thank you

 

[PCosta87]

thank you

Don't mention it... glad I could help