Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new row
I think this is very simple, (I'm new to VB)
I have a simple macro button that copies data from a row in a sheet A and then copies it to a row in another sheet B. However I dont want it to overwrite any data in sheet B, but to add it to the next available row. ANy help would be greatly appreciated guys. Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new row
Matt,
Try this: Sub CopyRowInsertAtBottom() Dim lRow As Long Sheets("Sheet A").Rows("1:1").Copy With Sheets("Sheet B") lRow = UBound(.UsedRange.Value) + 1 .Rows(lRow & ":" & lRow).Insert End With End Sub David Miller |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new row
On 1 Feb, 13:23, "Dave Miller" wrote:
Matt, Try this: SubCopyRowInsertAtBottom() Dim lRow As Long Sheets("Sheet A").Rows("1:1").Copy With Sheets("Sheet B") lRow = UBound(.UsedRange.Value) + 1 .Rows(lRow & ":" & lRow).Insert End With End Sub David Miller Thanks David, that works great, how could I use something similar to copy cells not neccessarily in the same row, so maybe A1 and but then B3, but then put them into a row and paste them into the next available row on another sheet. Basically I now have a form that people fill in then I want a macro to copy the form details into rows onto one master table. the code you sent me works for rows can i specificy just cells. Also does it work to another workbook alltogether not just another sheet in the same book. Thanks again for you help, brilliant Matt |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new row
Matt,
You could set the values of A1 (=A2) and B1 (=B3) to equal the values on your form. I would then hide that row, and just copy rows("1:1") every time. To copy from one workbook to another, try this: With Windows("OtherWorkbookName.xls").Sheets("Sheet B") lRow = UBound(.UsedRange.Value) + 1 .Rows(lRow & ":" & lRow).Insert End With David Miller |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new row
On 1 Feb, 14:57, "Dave Miller" wrote:
Matt, You could set the values of A1 (=A2) and B1 (=B3) to equal the values on your form. I would then hide that row, and just copy rows("1:1") every time. To copy from one workbook to another, try this: With Windows("OtherWorkbookName.xls").Sheets("Sheet B") lRow = UBound(.UsedRange.Value) + 1 .Rows(lRow & ":" & lRow).Insert End With David Miller Thanks again David, brilliant about the issue of copying the row, that works a treat. With regards to opening another workbook it doesn't seem to be working can you have a look for me:- Sub Button6_Click() Dim lRow As Long Sheets("Sheet A").Rows("1:1").Copy With Windows("Book4.xls").Sheets("Sheet B") lRow = UBound(.UsedRange.Value) + 1 .Rows(lRow & ":" & lRow).Insert Application.CutCopyMode = False End With End Sub I get an runtime error 9 ?. Am I missing something. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a new row
Matt,
I am sorry I was assuming the other workbook was already open. Try this: Sub Button6_Click() Dim sFile As String, _ lRow As Long sFile = "C:\DirectoryOfTheFileYouWantToCopyTo\File.xls " Sheets("Sheet A").Rows("1:1").Copy With Workbooks.Open(sFile) With .Sheets("Sheet B") 'If it is the first sheet you could use .Sheets(1) lRow = UBound(.UsedRange.Value) + 1 .Rows(lRow & ":" & lRow).Insert Application.CutCopyMode = False End With End With End Sub David Miller |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding 1 | Excel Programming | |||
Adding Up! | Excel Discussion (Misc queries) | |||
Adding an OR to SUM(IF.... | Excel Worksheet Functions | |||
Adding hrs | Excel Worksheet Functions | |||
Adding * | Excel Discussion (Misc queries) |