Copy and paste a cell value into next empty cell of a range

[nbuddhi]

Dear Team,

I need to copy the value of V80 into next empty cell of range A80:A89. Could you please help me to specify the cell range A80:A89 with below code or new code.

Private Sub CBCoppysummary_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Test Report")
Set pasteSheet = Worksheets("Test Report")

copySheet.Range("V80").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Thanks & Best Rgds,
Nuwan.

 

[Fluff]

What should happen if there are no blank cells in A80:A89?

 

[nbuddhi]

What should happen if there are no blank cells in A80:A89?

Hi Fluff,

Thank you for your reply. It is guarantee that data will be filled till A87 always

 

[Fluff]

Are you saying that there will always be at least blank in that range?

 

[nbuddhi]

Are you saying that there will always be at least blank in that range?

Yes, exactly

 

[Fluff]

In that case try

Private Sub CBCoppysummary_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Test Report")
Set pasteSheet = Worksheets("Test Report")

pasteSheet.Range("A80:A89").SpecialCells(xlBlanks)(1).Value = copySheet.Range("V80").Value
Application.ScreenUpdating = True
End Sub

Although I'm not sure why you are using two variables for the same worksheet

 

[nbuddhi]

In that case try

Private Sub CBCoppysummary_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Test Report")
Set pasteSheet = Worksheets("Test Report")

pasteSheet.Range("A80:A89").SpecialCells(xlBlanks)(1).Value = copySheet.Range("V80").Value
Application.ScreenUpdating = True
End Sub

Although I'm not sure why you are using two variables for the same worksheet

Thank you very much Fluff. It worked like a charm. Actually I'm using same code for different sheets as well. Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.