Copying inserted last line data from one work book to the last available line in another workbook

lakelands

Board Regular
Joined
Jun 12, 2003
Messages
126
Currently have a workbook with a macro button on a sheet which picks up data from that sheet and inserts it into the next available line on another sheet called Database in the same workbook as follows:

Dim lngLastRow As Long

With ActiveWorkbook.Sheets("Database")
lngLastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Range("A" & lngLastRow) = ActiveSheet.Range("E1")
.Range("B" & lngLastRow) = ActiveSheet.Range("B3")
.Range("C" & lngLastRow) = ActiveSheet.Range("B1")
.Range("G" & lngLastRow) = ActiveSheet.Range("E5")
.Range("H" & lngLastRow) = ActiveSheet.Range("D53")
.Range("I" & lngLastRow) = ActiveSheet.Range("B55")
.Range("J" & lngLastRow) = ActiveSheet.Range("E2")
End With
ActiveWorkbook.Save
Range("E1").Select
End Sub

I have a very basic macro which then which copies the whole Database from the original active workbook file to a workbook called Jobs History (with the same Sheet name) and then saves the Jobs History workbook as follows:

Sheets("Database").Select
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\XXXXX\Dropbox\Jobs History.xls"
Range("A3").Select
ActiveSheet.Paste
Range("A3").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\XXXX\Dropbox\Jobs History.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Sub

I am sure their is an easier way just to find the next available line in the Jobs History file and copy the newly created line from the Database in the original workbook to it and save it that way.

Any ideas would be appreciated
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top