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

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

On 1 Feb, 13:23, "Dave Miller" <david2.mil...@citigroup.com> 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

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

On 1 Feb, 14:57, "Dave Miller" <david2.mil...@citigroup.com> 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.

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