Copy & Paste a row underneath the last one in the table

**BouncingHippo** · 01-20-2008, 10:14 PM

Hi,

I'm trying to make a script that will copy and paste a row in a table underneath the last row in that table when a button is clicked. I've simply recorded a macro to get things rolling, however whenever the macro does an insert to paste the data it goes above the last new entry which doesn't work for my purposes, any help would be appreciated.

Please Login or Register  to view this content.

**stevekirk** · 01-20-2008, 11:28 PM

hi,

this will put data into the last empty row in column a

Please Login or Register  to view this content.

steve

**stevekirk** · 01-20-2008, 11:38 PM

hi,

this macro let you select the row and then paste it in the next empty row
you click on the row and run macro

Please Login or Register  to view this content.

steve

**BouncingHippo** · 01-21-2008, 12:38 AM

Thanks for the help Steve.

I think i misconveyed my problem. When I said copy the row to the last blank space on the table i meant the last blank space under the table i've made within the sheet that then has a couple of black spaces and then formulas to total figures and such.

I need a way of placing the copied row directly under the one just copied and also inserting space underneath the new row so the new rows don't wipe out the formulas below the table.

**stevekirk** · 01-21-2008, 01:50 AM

hi try this
click on the row before you run the macro

Please Login or Register  to view this content.

steve

**stevekirk** · 01-21-2008, 01:50 AM

hi try this
click on the row before you run the macro

Please Login or Register  to view this content.

steve

**royUK** · 01-21-2008, 02:30 AM

Originally Posted by BouncingHippo

Thanks for the help Steve.

I think i misconveyed my problem. When I said copy the row to the last blank space on the table i meant the last blank space under the table i've made within the sheet that then has a couple of black spaces and then formulas to total figures and such.

I need a way of placing the copied row directly under the one just copied and also inserting space underneath the new row so the new rows don't wipe out the formulas below the table.

I would suggest you rethink the design of your table. When working with tables of data I always have 4/5 empty rows above the table reserved for calculations. That way as the table grows yoy don't need to keep adding rows aand by using Freeze Panes the totals are always available.

No VBA is really necessary if you make sure that in Tools>-Options>-Edit tab the "Extend data range formats & formulas" is selected, Excel will automatically do this.

In Excel 2003 you can use the List feature

http://www.excel-it.com/excel2003_lists.htm

**BouncingHippo** · 01-21-2008, 08:25 PM

Hi,

I'd like to redesign the form but it has to be done in a certain way.

Ok this is the code i've made for adding the row however i'm getting compile errors and I can't get it to work no matter what i try, maybe someone can help me out. Errors happen at Range and Rows

Sub NewVar()
'
' NewVar Macro
Dim rval As Integer

'row that search begins in'
rval = 18
'1 is the beginning column "" makes it go to the nearest empty cell in the search'
While Not Cells(rval, 1).value = ""
rval = rval + 1
Wend
'Copies the row above'
rval = rval - 1

Sheets("Sheet7").Select
Range(rval, 11).Select
Selection.Copy
rval = rval + 1
Rows(rval, 11).Select
Selection.INSERT Shift:=xlDown
Application.CutCopyMode = False

End Sub

**stevekirk** · 01-21-2008, 10:30 PM

did you try the macro i posted- if so did it not work?

steve

**BouncingHippo** · 01-21-2008, 10:39 PM

Yes I tried your macro and no it didn't work. I kept getting For errors.

My code works accepts for the range and rows errors, I need to know if i should be declaring things in a different way to get rid of the errors.

**stevekirk** · 01-21-2008, 11:46 PM

hi try this

the macro i gave you had a error in it

Please Login or Register  to view this content.

steve

**stevekirk** · 01-21-2008, 11:58 PM

hi,

this macro will not prompt you for the no of rows it will insert 1 just click on the row before runng the macro

Please Login or Register  to view this content.

**BouncingHippo** · 01-22-2008, 10:25 PM

Thanks, the code now works. ...thanks

One other thing that you might be able to help me with.

I created the macro to goto a sheet copy that sheet and then insert a new sheet and paste the copied information. That part works fine, however I need to link back fields from the newly pasted form back to the a new row which is created with the code above

The code to copy from the template form and inser below i need code to get the information back to the "register" sheet and place it into the appropriate boxes in the new row....is that possible?

Please Login or Register  to view this content.

**stevekirk** · 01-23-2008, 04:02 PM

hi,

a bit confused- you have inserted a new row from the macro i gave you and you have copied information from one row to another.

have you in your macro copied that sheet? if so why dont you copie it after the first macro has finished?

steve

**BouncingHippo** · 01-23-2008, 06:41 PM

hi,

a bit confused- you have inserted a new row from the macro i gave you and you have copied information from one row to another.

have you in your macro copied that sheet? if so why dont you copie it after the first macro has finished?

steve

Hi Steve,

Yeah it is a bit confusing to follow after i re-read my post. Yes I have copied information from the row above, however the links in the new row link to another sheet and not the one just created.

I need a way of Creating the sheet and then updating the links in the copied row to link to the new sheet.

btw is there also a way to renaming a tab like if the one before it was VQ-15 can I somehow make excel auto make the next new sheet created VQ-16 ?

**BouncingHippo** · 01-23-2008, 10:17 PM

Hi,

I've worked out how to change the tab names and semi worked out how to get the data to copy from the new sheet to the new rows.

One thing i need to know in order to complete my code is a line of code to copy a variable from one sheet to a cell in another sheet which is then referenced to change the data in the rows.

**stevekirk** · 01-23-2008, 11:29 PM

hi,

at present do all the links in the table go to the same worksheet?
why are we creating a new sheet and when
it seems that you are creating a sheet just for the row that was copied via the macro!

steve

**BouncingHippo** · 01-24-2008, 12:33 AM

Hi,

Each link goes to a different worksheet as they are variation sheets.

The Workbook is Setup as follows:

Register Sheet - This has the table where we are creating a new row each time when the code u gave me is run.

The new row is for the new variation that is being created and then specific cells in the new row need to link to specific cells in the newly created sheet.

Variation Sheet - Stores all the core data that is linked back to the register and to a particular row, each variation sheet has new data.

I was just thinking that you can get the info to link back if you used a script similar to the "InsertRowsAndFillFormulas" and then instead of creating a new row you go up a row and then link each cell from the new sheet to the register.

i.e

##Tab Works##

Please Login or Register  to view this content.

##Copy Cells doesn't work with the way i'm doing it and i don't know vba well enough to manipulate your previous code to make my idea work###

The Idea with rval is that it finds the last empty row and then goes back one to place the code on the newly create row and then uses the var VQ to place the new location of the sheet that's being referenced.

My rval code doesn't work but you're code does the same thing but as I said i'm not sure how to manipulate it to make it work with my code.

Sub CopyCells()
'
' CopyCells Macro
Dim rval As String

ID = Cells(1, 22).value

If ID = 1 Then
Sheets("Register").Select
rval = 10
While Not Cells(rval, 1).value = ""
rval = rval + 1
Wend
rval = rval - 1

End If

VQ = Cells(1, 20)

ActiveCell.FormulaR1C1 = "='VQ'!R[-17]C[-1]"
Cells(rval, 5).Select
ActiveCell.FormulaR1C1 = "='VQ'!R[12]C[6]"
Range("rval,1").Select
ActiveCell.FormulaR1C1 = "='VQ'!R[-18]C[1]"
Range("rval,2").Select
ActiveCell.FormulaR1C1 = "='VQ!R[-22]C[8]"
Range("rval,2").Select
End Sub

I hope things are more clear now

**stevekirk** · 01-24-2008, 01:20 AM

BouncingHippo

Please read forum rules and wrap your code next time

hi,

we will try one bit at a time

put this code after "next" in the macro but before end sub

Please Login or Register  to view this content.

this adds another sheet and copies the last row into the new sheet and it is linked

you must put code tags around your code

let me know if thos part is correct

steve

**BouncingHippo** · 01-24-2008, 01:59 AM

Hi,

Thanks for the code but I don't think you understnad what i'm trying to do, So what i'll do is post the workbook and you'll have a clearer idea of what i'm trying to do.

I've had to seperate the files because for some reason they wouldn't zip at under 100k when they were both in the same work book. Register is the sheet that all the VQ's will link back to.

The VQ is the sheet that you keep replicating.... after you've had a look you can make some comments and we'll go from there

...Thanks for the help!!

I've added a few notes in the excel doc. Once you have a look at what i'm trying to do u may understand a bit better. btw just copy and paste the VQ into the Construction Variation Workbook and call the tab something like VQ-01.
Cheers.

**BouncingHippo** · 01-24-2008, 05:59 PM

btw sorry about not wrapping my code.

In regards to the code you posted it was correct in the fact that it copied the row data to another sheet. Maybe a jumped the gun when i said you didn't understand what I was trying to do

As always any help is appreciated.

**stevekirk** · 01-24-2008, 08:16 PM

hi,

so we have the table that puts the new row in with data from the row above
we copy that data to a worksheet that we added called vq-??! we then have another workbook called register that needs to link to the data we put into the new VQ-?? worksheet

does the register start at row 9 and have all the VQ-?? date in it

steve

**BouncingHippo** · 01-24-2008, 08:48 PM

Hi,

Yes that's pretty much correct accept the new VQ-? just copies the previous VQ-whatever and doesn't copy the data from the new row to it. The VQ-? just needs to have links created linking certain cells back to the new row so any information inputted in the VQ-New will copy back to the register.

Yes, it starts at row 9 with the Titles for each section of information. The information that needs to link back to the table row from the VQ-? is Ref No, Date, Details and Amount. Nothing else needs to link back.

**stevekirk** · 01-25-2008, 12:26 AM

HI,

lets see if we have got a it more.

Please Login or Register  to view this content.

in you VQ put links in m1 to p1 from your date, ref, and details ,

this macro will then put the links in the new row in the table from the cells m1 to p1 . this way it is easier

this macro looka for a sheetname "table " so alter it to suit your table sheet name or change the sheet top table

you have to have a sheet called "vq-00 or greater.

i had help from rylo with the sheet name

let me know if ok and then we wil do the last part

steve

**BouncingHippo** · 01-25-2008, 01:22 AM

Hi Steve,

Thanks for the help. This code sort of works, while it copies the row and links it back to the new sheet it's linking the new row to cells such as P1 or Q1 which isn't really workable with the VQ form layout. The links being made would need to be

C14 for Ref No
C15 for Details
J9 for Variation No
J10 for Date
K44 for Amount

Also when I run the current script it tries to fill the whole row it only needs to be filled from columns A to K

I do appreciate the effort your putting into this.

Thanks

**stevekirk** · 01-25-2008, 01:49 AM

hi,

the problem is that you will be going from sheet to sheet putting each link in as they are not in a row.

why cant you put the links in other cell just to hide them ie aa1 to ac1
in cell aa1 put = c14 in ab1 put = c15 etc

when you type in c14 it will go to aa1 and the link vis the macro will send it to vq we will alter the a to k

steve

**BouncingHippo** · 01-28-2008, 06:32 PM

Hi Steve,

thanks for the help, just one thing when the vba links back to the register it copies the range from column a to column IV in the new row which way outside the range of what i need. is there a way of limiting it so that when things are linked back to the register that it only links from column a to e in the register ?

Thanks again!

**stevekirk** · 01-28-2008, 08:30 PM

HI,

put this

Please Login or Register  to view this content.

after

Please Login or Register  to view this content.

hope this does it

steve

Copy & Paste a row underneath the last one in the table

LinkBack

Thread Tools

Rate This Thread

Display

Copy & Paste a row underneath the last one in the table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions