Copy and Paste in adjacent cell using "first emply cell"

[SAMCRO2014]

I am stumped on how to code something. I am using copy and paste from Worksheet “SPS”and pasting the data to the first blank cell of a specific column in Worksheet “Data”.I had no problems figuring this out except formy last column.
I want to find the first empty cell in column N and paste the information starting in the adjacent cell in Column O.

This is what I have:

'Copy and paste Projection Amount Data
Dim SPS As Worksheet
Set SPS = ThisWorkbook.Sheets("SPS+")
Dim lastO As Long
LastRowSPS2 =SPS.Cells(Rows.Count, 1).End(xlUp).Row

Range("N3:N" &LastRowSPS2).Copy
With Data
lastO =.Range("N" & Rows.Count).End(xlUp).Row + 1
.Range("N"& lastO).PasteSpecial xlPasteColumnWidths
.Range("N"& lastO).PasteSpecial xlPasteValues
End With

ActiveSelection.Cut
ActiveSelection.Offset(0,1).PasteSpecial xlPasteValues

 

[James006]

Hello,

If you are looking for the First Blank Cell ...

[COLOR=#303336][FONT=inherit] FirstBlankCell[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"N2:N"[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Cells[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]SpecialCells[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlCellTypeBlanks[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Row[/FONT][/COLOR]

HTH

 

[SAMCRO2014]

I can find the first blank cell in Column N but how to I then get the macro to paste the information in the adjacent cell in Column O. There is no data in column O and I need the information to paste in a different cell each time the macro is run.

 

[James006]

Sorry ... but your objective is not clear to me ...

If you have located the correct Row in column N ... and need to store something in the adjacent cell in Column O ...

you can use the Offset function ... Offset( Range,0,1) ...

HTH

 

[SAMCRO2014]

I have salary actuals posted in column N. The number of rows will change constantly. Column O is the amount of salary we projected to spend for the same time frame. So I need the paste the projected salary amounts in first blank cell adjacent to column N...in Column O. I have a macro written to create a pivot table with these two columns being used for the values and creating a calculated field to show the variance.

 

[James006]

Sorry ... I cannot understand what you are looking for ...

Hope somebody else will sort it out for you ...

 

[SAMCRO2014]

Thank you for taking the time to help me!