Copy All But the First Row of a Table Databody and Append to the End of Another Table

**TheScott** · 06-20-2015, 10:44 AM

I have a small problem that I can’t seem to resolve. I have two tables. One is a master record and the other monthly transactions. The monthly table starts with an ‘initial’ balance (which is the basis for a running balance) that is also the closing balance of the previous month. At the end of each month I want to copy the all rows of the monthly transactions to the master record… except for the very first row.
When I copy the first row, it is, in essence a copy of the very last row of the previous month… This screws up running balances and totals.

My question: How would you append all rows of one table to the bottom of another minus the, of course, the headerrow and the 1st row of data.

AppendAllButFirstExp..xlsm

Thanks in Advance
Scott

**Ron Coderre** · 06-20-2015, 11:18 AM

Using your posted workbook...try something like this:

• Select the workbook you want to contain the macro
• ALT+F11…to open the VBA editor
• Insert.Module…to create a new module
• Copy the below VBA code and paste it into that module

Please Login or Register  to view this content.

Is that something you can work with?

**excelliot** · 06-20-2015, 11:21 AM

Dont copy paste directly, but copy data except op. balance (J5 to N12) goto last cell in your case (A11) then paste only values by pressing Alt > E > S > V, once you paste values all formatting will be copied automatically. After that goto cell E5, copy E5 & select E5 to E8 & press Ctr + V together...

Let me know if its not working...

**TheScott** · 06-20-2015, 11:44 AM

Ron,

That works perfectly. I was certainly looking for a VBA solution which is why I posted in this forum. I suppose I should have posted my current code to be modified but wasn’t sure it didn’t need a complete rewrite to achieve your results.. it didn't need to be changed at all as it turns out, just added to.

Intersect(ActiveCell.EntireRow, tblDest.DataBodyRange).Delete

Intersect is a new function for me…. Before answering your post I did a little bit of reading but one thing I can’t get my head around is how we know that the ‘activcell’ is in the first row of the copied range…. Is it because the only row in the copied range that intersects another range is the first row? Is the upper left cell in a range considered to be the active cell?

Inquiring but generally confused minds……

Thanks for your assistance
Scott

excelliot - I am sorry that I didn’t make myself clear in that I was looking for a VBA solution. Doing it manually is not a problem…

Thank You for your time and input

Scott

**Ron Coderre** · 06-20-2015, 12:01 PM

When you paste, Excel selects the destination range. The upper left cell of that range becomes the ActiveCell. Consequently, we just select the intersection of the ActiveCell's entire row and the DataBodyRange to return that one row of the DataBodyRange. Using Delete, that one table row is deleted.

Does that help?

**TheScott** · 06-20-2015, 01:38 PM

Ron

Hopefully you will see this as I had already marked the thread as solved but I made one major error in presenting my example.... The tables actually reside on different worksheets and it seems that intersect doesn't play well across worksheets. If you see this, I would appriciate some guidance.

Regards

Scott

**Ron Coderre** · 06-20-2015, 06:53 PM

The problem is not with Intersect...It occurs because both tables are not on the same worksheet AND we've only defined a single worksheet object.

try this...Assuming that TblMonthly is on Sheet2:

Please Login or Register  to view this content.

**TheScott** · 06-21-2015, 02:43 AM

I am amazed and quite confused.

Other than a slight naming difference of worksheets and a reverse order of the worksheets our code was identical…. Yesterday I got errors: ‘run-time error 1004 Method Intersect of object _Global failed’. Research into this error lead me to believe that the problem was with the use of intersect…. Yet, today, I rearrange the sheet names, try your code and it works perfectly…. I change the sheet names back to the way they were and try my code and it too worked without a flaw….

I am sorry for wasting your time in revisiting this problem but I am at a loss as to what I did wrong but it is obvious… I did something wrong.

Late Edit:
I tried applying this to my project and came back to the same runtime error. I found out what my problem was. The code wants to be run with the master record 'wksdest' active. I need for it to run from the source, which could by one of many, so I just needed to add a 'wksDest.activate' before the With statement and it runs fine from the active source sheet.

Thanks again

Scott

Copy All But the First Row of a Table Databody and Append to the End of Another Table

LinkBack

Thread Tools

Rate This Thread

Display

Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Re: Copy All But the First Row of a Table Databody and Append to the End of Another Table

Thread Information

Users Browsing this Thread

Similar Threads

Copy cells append to table

[SOLVED] Append table values from one workbook to existing table in another workbook

Access VBA or SQL to add all records from a table to another table (Append)

append data into a table

How can i Append to Table using table Variable name

Bookmarks

Bookmarks

Posting Permissions