davidboutche
08-10-2009, 07:00 AM
I have two excel spreadsheets. The first one holds my primary data.
The second spreadsheet is based on external data in sharpepoint are basically the same.
I'd like to use a macro that will copy the information from the first spreadsheet onto the next available line on the second sheet.
I have used the following which does this, but it places it outside of the 'square' that is controlled by sharepoint.
Sub copyalltoend()
Dim LastRow As Object
Dim ws As Worksheet
Workbooks.Open Filename:="V:/pndbook.xls"
Do
Rows("2:2").Select
Selection.Cut
Windows("Sharepnd.xls").Activate
Set ws = ActiveWorkbook.Worksheets("CAHW8F11")
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Insert
Windows("pndbook.xls").Activate
Selection.Delete Shift:=xlUp
Windows("pndbook.xls").Activate
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Windows("pndbook.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Sharepoint has also made it's own unique key field in the first columb that stops the data matching up.
Does anyone know of a solution?
The reason I'm doing this is so that multiple users can work on the data at anyone time as I believe if more than one person tries to open the spreadsheet in excel they will all be read only apart from one user. I'm open to any better solutions.
The second spreadsheet is based on external data in sharpepoint are basically the same.
I'd like to use a macro that will copy the information from the first spreadsheet onto the next available line on the second sheet.
I have used the following which does this, but it places it outside of the 'square' that is controlled by sharepoint.
Sub copyalltoend()
Dim LastRow As Object
Dim ws As Worksheet
Workbooks.Open Filename:="V:/pndbook.xls"
Do
Rows("2:2").Select
Selection.Cut
Windows("Sharepnd.xls").Activate
Set ws = ActiveWorkbook.Worksheets("CAHW8F11")
Set LastRow = ws.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Insert
Windows("pndbook.xls").Activate
Selection.Delete Shift:=xlUp
Windows("pndbook.xls").Activate
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
Windows("pndbook.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
Sharepoint has also made it's own unique key field in the first columb that stops the data matching up.
Does anyone know of a solution?
The reason I'm doing this is so that multiple users can work on the data at anyone time as I believe if more than one person tries to open the spreadsheet in excel they will all be read only apart from one user. I'm open to any better solutions.