Hi,

I am trying to sort a timesheet spreadsheet for my work.
What I am after is simple in itself but with the guys I work with I need to make it simpler.
What I want to do is 'cut' the table (weekly timesheet) from sheet 1 and 'paste' it to the top of sheet 2. Meanwhile in sheet 1 the table below the now 'cut' table moves up to the top of the page. While the tables in sheet 2 move down for the new 'pasted' table.
The catch is, I would like there to be a 'refresh' button to do this. That way the boss can click refresh once he has reviewed the tables. So each week, he goes in, ckecks it out, hits refresh and the cut and paste happen. Then repeat the following week.
Is this at all posible?

Thanks in advance for any help given.

Hi,

The trick involves identifying the top non empty cell in sheet 1 and the bottom non empty cell in the sheet2.

Once you have identified the top non empty cell (start point of the new table) use the currentregion property (which will select the entire block of contiguous columns and rows, i.e. the table). Cut the selection.
Go to table 2 look for the last row where there is any data available. Go 1 row down this will be the place you will paste the cut table from sheet 1. You put the whole process in a loop which will continue running until it finds no tables left in Sheet1 to be cut and pasted into Sheet2.

Does it sound like a plan? I am assuming you are able to write the equivalent VBA code but if that's not the case please let me know and I can assist you with the VBA code as well.

Thanks
Prashant

First of all thanks for the reply. This sounds like what I am after.
however, I am a nube and don't know VBA but I think I know what you're talking about. How would I go about doing this?

Hi,

Good to know that this was helpful.

Are you able to send me a sample spreadsheet - I just don't want to make guesses and ending you up with updating my VBA. I will provide you with the equivalent VBA.

Thanks,
Prashant

Again, much appreciated. Attached is the relevant spreadsheet.
So what I am after is each time my boss reviews the spreadsheet, he can hit a refresh button or something and the top table from sheet 1 gets posted in sheet 2 and the next table in sheet 1 moves to the top of the page.

cheers

Hello,

Looking at the template, each table seems to have the same number of rows. If you can rely on the data to always contain the same number of rows, then this code will work fine:

Sub MoveToSummary()

Sheet1.Range("1:25").Cut Sheet2.Range("A1")
Sheet1.Range("2:25").Delete

End Sub

Otherwise, the following code is a bit more flexible:

Sub MoveToSummary()
Dim rTable As Range

Set rTable = Sheet1.Range("C1").End(xlDown).CurrentRegion
rTable.EntireRow.Cut Sheet2.Range("A2")
rTable.EntireRow.Delete

End Sub

Hopefully one of these methods will work out for you.

Ben

Hi Ben,

The second code works real well. But is it possible to 'insert' the newly cut table as opposed to paste?
That way the previously cut table shifts down a few rows?

Many thanks.

Sure thing. This worked for me.

Sub MoveToSummary()
Dim rTable As Range

Set rTable = Sheet1.Range("C1").End(xlDown).CurrentRegion
Sheet2.Range("2:2").Insert
rTable.EntireRow.Cut
Sheet2.Range("2:2").Insert
rTable.EntireRow.Delete

End Sub