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
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