Adding a new row

[matthew.newsome]

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

 

[Dave Miller]

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

 

[matthew.newsome]

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

 

[Dave Miller]

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

 

[matthew.newsome]

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.

 

[Dave Miller]

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