Count, insert row, paste in other tab

Question

I am trying to create a code that looks at column A in sheet1, then insert new lines in sheet2. Then paste the contents of column A (sheet1) into column A on sheet2. See attached pictures: Sheet1 - total list Sheet2 - existing list, need to add new lines from sheet1 and shift down the rest of contents. Sheet3 - Result.

I do this manually every time but I am trying to make it automatic so I can save some time. I will assign a button to this.

Thanks in advance. Nelson

enter image description here

Do you have some sample code for us to look at that you attempted? You could also look at this link for some ideas https://stackoverflow.com/questions/43468467/copy-sheet-content-to-another-sheet/43469300#43469300 — CAMD_3441, Mar 21 '18 at 14:51

LatifaShi · Accepted Answer · 2018-03-22T17:58:00.347

0

try the code below :

Sub test()

            Dim ws As Worksheet
            Dim ws2 As Worksheet

            Set ws = ThisWorkbook.Sheets("sheet1")
            Set ws2 = ThisWorkbook.Sheets("sheet2")

            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            lastRow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row

            i = 1
            Do While ws.Cells(i, 1).Value = ws2.Cells(i, 1).Value
                 i = i + 1
            Loop

            For j = i To lastRow
                lastRow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
                ws2.Rows(lastRow2 + 1).EntireRow.Insert
                ws2.Range("A" & lastRow2 + 1).Value = ws.Range("A" & j).Value
            Next j

        End Sub

edited Mar 22 '18 at 17:58

answered Mar 21 '18 at 14:53

LatifaShi

440
1
3
12

1

`lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row` – braX Mar 21 '18 at 14:58
1

You're right (y) .. – LatifaShi Mar 21 '18 at 14:59
Thanks. But it pasted the values but I would like to insert entire rows so that everything below in sheet2 get push down and that way I don't loose formulas. – Nelson Veras Mar 21 '18 at 15:17
I'm not sure if I understand you .. do you mean the sheet2 is not empty ? and you want to add the values of column A in sheet1 to column A in sheet2 above the existing values ? – LatifaShi Mar 21 '18 at 15:22
ok, so let me try to explain... I have a list of orders that increases every month so all I want to do is add the new ones but that is a section of sheet2 so I will like to add rows so I can push the bottom down instead of copying over. – Nelson Veras Mar 21 '18 at 15:26
Can I send you a file so you can see what I mean? – Nelson Veras Mar 21 '18 at 15:27
I edited my answer, check it and give me a feedback .. – LatifaShi Mar 21 '18 at 15:31
This is close. is there a way we can have it to look at the latest order on sheet 2 and olnly add the new ones? for example, I have orders 1 thru 20 and now this month I have 1 thru 25, can I have it to just add 5 rows after the last one (20) and paste the new 5 values? Usually column A is only order numbers nothing else so it can look at the latest entry on sheet2. – Nelson Veras Mar 21 '18 at 15:40
So you need to add your entires at the end of sheet2 not at the top .. and you know your latest entry based on what ? the lastrow in sheet2 ? .. I need to see an example of what you want .. – LatifaShi Mar 21 '18 at 15:49
can I send you and email with a file? – Nelson Veras Mar 21 '18 at 16:30
That is correct. I need to add entries at the end of sheet2, I would like it to compare to what I already have in sheet2 and add if there is new ones. sheet1 will always have the same or more than sheet2. – Nelson Veras Mar 21 '18 at 17:26
Hi, are you trying something? @LatifaShi ? – Nelson Veras Mar 22 '18 at 12:35
I am sorry I wasn't home .. check again my edited answer .. Hope it is what you are looking for this time .. – LatifaShi Mar 22 '18 at 17:52
it brings the contents good but it doesnot insert rows for those new ones...it just past them below the last one – Nelson Veras Mar 22 '18 at 18:00
check my answer again I added a line : ws2.Rows(lastRow2 + 1).EntireRow.Insert – LatifaShi Mar 22 '18 at 18:05
That's Perfect!!! Youre the best!!!!!! OMG you are going to save me hours of work!! – Nelson Veras Mar 22 '18 at 18:22
Sorry, I didn't say Thank You...too excited! – Nelson Veras Mar 22 '18 at 18:23
I have another little request...can I bring the formulas from above to the new lines? starting in column B of sheet2? – Nelson Veras Mar 22 '18 at 18:25
You're welcome :). By the way, you could mark The answer as accepted for others by clicking the check mark next to the answer. For your other request i'm sorry but I have never worked with formulas in excel so I'm not sure about How to manipulate them with VBA :p .. – LatifaShi Mar 22 '18 at 18:40
No problem. Thanks!! – Nelson Veras Mar 22 '18 at 18:42
You're welcome ^^ .. – LatifaShi Mar 22 '18 at 18:53

Count, insert row, paste in other tab

1 Answers1