copy and paste cells after finding last row

bthyaabd

New Member
Joined
Nov 16, 2022
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
Hi,

with the code below i am trying to find a specific cell, offset it and paste from cell c11 and c13.
i've set the coloumn AE as the counter column (this part of the code works fine it finds the last row and add 1 to value of the previous.

but the paste part of the code keeps on pasting the values from c11 and c13 in row 45,

ie. the last row of AE will be row 3 but the pasting will still be on row 45

any help please.



Private Sub editprice_Click()


Dim rngFindValue As Range
Dim rngSearch As Range
Dim rngFind As Range
Dim lastrow As Long


On Error Resume Next
Application.ScreenUpdating = False


Set rngFind = ActiveSheet.Range("AD22:ZE22") 'search in this range of cells
Set rngSearch = rngFind.Cells(rngFind.Cells.Count) 'get the number of cells in this column
Set rngFindValue = rngFind.Find(Range("c9").Value, rngSearch, xlValues) 'search for the value that in cell c9

'================================================================================
' finding the last row in column AE

With ActiveSheet
lastrow = .Cells(.Rows.Count, "AE").End(xlUp).Row
End With
'========================================================

'making the no. column automated increasing no.

If IsNumeric(Range("AE" & lastrow)) = False Then

ActiveSheet.Cells(lastrow + 1, "AE").Value = ActiveSheet.Cells(lastrow + 1, "AE").Row - 23

Else

ActiveSheet.Cells(lastrow + 1, "AE").Value = ActiveSheet.Cells(lastrow, "AE").Value + 1

End If
'============================================================================================

ActiveSheet.Range("c13").Copy
rngFindValue.Offset(lastrow, 0).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ActiveSheet.Range("c11").Copy
rngFindValue.Offset(lastrow, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'===================================================================================================================



End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try this:
VBA Code:
Sub Copy_To_Lastrow()
'Modified 11/18/2022  11:55:20 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "AE").End(xlUp) + 1
Range("C11").Copy Cells(Lastrow, "AE")
Lastrow = Cells(Rows.Count, "AE").End(xlUp).Row + 1
Range("C13").Copy Cells(Lastrow, "AE")
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this:
VBA Code:
Sub Copy_To_Lastrow()
'Modified 11/18/2022  11:55:20 PM  EST
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "AE").End(xlUp) + 1
Range("C11").Copy Cells(Lastrow, "AE")
Lastrow = Cells(Rows.Count, "AE").End(xlUp).Row + 1
Range("C13").Copy Cells(Lastrow, "AE")
Application.ScreenUpdating = True
End Sub
i need to find a value of cell c9 first and relatively to the position i need to offset and paste
 
Upvote 0
I'm sorry I'm just not following what your ultimate goal is:
If you would just say I want to copy this range and paste in in this column first empty cell, I may be able to help

You said:
i need to find a value of cell c9 first and relatively to the position i need to offset and paste
What does all this have to do with it?
Lastrow = .Cells(.Rows.Count, "AE").End(xlUp).Row
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top