EXCEL HELP PLS. Find Last Record on Sheet and Copy/Multiple to Rows in Another

[Fadil]

I am trying to find a code / macro that will do the following and was hoping that someone of you Guru's can help me out.

I have a sheet with many records and I want a macro to find the last record of that sheet, copy that record and paste it in multiple cells/rows in another sheet until the last record of the destination sheet

I am attaching a sample spreadsheet to what I am looking for to do.

Your help is always gratefully appreciated

With respect,
Fadil

 

[Luke M]

I don't completely understand from your example how you know where/what to copy, but here's a start.

Sub CopyRec()
Dim lastRec As Range
Dim lastRow As Long

With Worksheets("Detail")
    'Find the last record
    Set lastRec = Worksheets("Main").Cells(.Rows.Count, "C").End(xlUp)
    'Find how many rows we need to paste to
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Copy the record
    lastRec.Copy .Range("E2:E" & lastRow)
End With
End Sub

 

[Fadil]

Hi Luke,

Thank you very much. This is a big help indeed. I was able to just copy and paste the formula just as it is.
However, I didn't think this through.

I hope it's not too much to ask because I can not figure it out on my own.

For example, if I ran the code once, and I retracted the last value from Main table (which the code is doing now). Now, if I want to keep these lines, and for any new lines I add in the Detail table, not to overwrite the old data, but to just retract the last value, how do I do that.

Because, if I add new lines now on Detail table, the code overwrites the old Range E and populates it with new data.

Regards,
Fadil

 

[Luke M]

What do you mean by "retract"?

If you mean to copy data to next blank column, we can do that.

Sub CopyRec()
Dim lastRec As Range
Dim lastRow As Long

With Worksheets("Detail")
    'Find the last record
   Set lastRec = Worksheets("Main").Cells(.Rows.Count, "C").End(xlUp)
    'Find how many rows we need to paste to
   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Copy the record, to next blank column
   lastRec.Copy .Cells(1, .Columns.Count).End(xlToLeft).Offset(1, 1).Resize(lastRow - 1)
End With
End Sub

 

[Fadil]

Hi Luke,
I really thank you for your time and efforts.
I know maybe I'm not being clear with my query, but I will try to explain it and I'm sorry I'm just not being able to tweak the current formula and apply it

From my Sample spreadsheet, in Sheet "Detail" in Column E I want to bring the last record from the Sheet "Main" Range C and multiply this record for the number of lines available in Sheet "Detail". Now the code is doing this.
What I wanted next is if I am adding additional record to Sheet "Detail" and now Range E is already populated with the previous data and I want to keep that info unchanged. But for whatever new rows are added in Sheet "Detail", the code to get the last record from Range C of "Main" Sheet and add in the next available row in Range E of "Detail" sheet without overwriting the previously added data.

Right now the code is overwriting the previous data and replacing it all with the new last row of Main sheet

Regards,
Fadil

 

[Fadil]

Basically, all I want to do next is the code
'Copy the record
lastRec.Copy .Range("E2:E" & lastRow)

In E2:E, not to overwrite the existing data for the above records, but just to take the last record from Main sheet, copy it and paste for the available number of new records on Details sheet

Thank you,
Fadil

 

[Luke M]

Now I'm more confused.
The ammended code I posted made sure that it wouldn't overwrite new data (assumes that you have column headers). Then you say that no, you want it to go in col E every time...but col E already has data!

Perhaps you could post a workbook/picture showing what it is you want to see happen exactly.

 

[Fadil]

on the code
'Copy the record
lastRec.Copy .Range("E2:E" & lastRow)

when it copies last record and pastes it in the "E2:E" column, when new record is generated, new line, new row in both sheets, I need the code to get the last lastrow from main sheet as it is now, and paste it below the last row in the column E of detail sheet

I will prepare the workbook and send you

 

[Fadil]

Here is the sample I tried to explain with comment and by highlighting the cells

 

[Luke M]

Perfect, that helped tremendously!

Give this a shot.

Sub CopyRec3()
Dim lastRec As Range
Dim lastRow As Long
Dim destRow As Long

With Worksheets("Detail")
    'Find the last record
   Set lastRec = Worksheets("Main").Cells(.Rows.Count, "C").End(xlUp)
    'Find how many rows we need to paste to
   lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   destRow = .Cells(.Rows.Count, "E").End(xlUp).Offset(1).Row
    'Copy the record
   lastRec.Copy .Range("E" & destRow & ":E" & lastRow)
End With
End Sub

 

[Fadil]

Luke,

Thank you very much. This code set it straight and it is doing exactly what I needed for this spreadsheet to work and I can move to next step.
As a thank you note, a small donation has been made on your name through this website link for donations.

Best regards,
Fadil

 

[Luke M]

Wonderful, glad I was able to get it figured out. Your donation is deeply appreciated.