Microsoft Office Forums - View Single Post

charlesdh · 10-01-2016, 10:35 AM

HI,

This code should copy over existing data

Code:

Sub newcsvtosharepoint()
'
' newcsvtosharepoint Macro
'

'This will paste ove existing data
    Dim lastrow As Long
Sheets("Master").Activate
'' This code is predicated on column A always having the last used row in the spreadsheet''
''
lastrow = Sheets("Master").Range("A65536").End(xlUp).row + 1 'we add 1 so that it will be the next empty row
Columns("A1:O" & lastrow).Select
Selection.Copy
    Workbooks.Open Filename:= _
        "https://sharepoint.fngn.com/departments/ced/CommunicationsDelivery/PrintProd/Reports%20Library/MasterTableDBase2016.xlsx" _
        , UpdateLinks:=3
   Sheets("Master").Activate
   '' this code is set to paste the data strarting in "A1". If you have a Header the it will be in row 2''
   '' you must be carefull this will paste over existing data''
   '' If the new data is suppose to go the next empty row the you must identify the next empty row.
   
    Columns("A1").Select '' paste over existing data do not use if you do not want to copy over''
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.CutCopyMode = False
End Sub

This code should copy to next empty row

Code:

Sub newcsvtosharepoint()
'
' newcsvtosharepoint Macro
'

'Thhis will copy data to the next empty row
 Dim lastrow As Long
Sheets("Master").Activate
'' This code is predicated on column A always having the last used row in the spreadsheet''
''
lastrow = Sheets("Master").Range("A65536").End(xlUp).row + 1 'we add 1 so that it will be the next empty row
Columns("A1:O" & lastrow).Select
Selection.Copy
    Workbooks.Open Filename:= _
        "https://sharepoint.fngn.com/departments/ced/CommunicationsDelivery/PrintProd/Reports%20Library/MasterTableDBase2016.xlsx" _
        , UpdateLinks:=3

   Sheets("Master").Activate
   '' this code is set to paste the data strarting in "A1". If you have a Header the it will be in row 2''

   '' If the new data is suppose to go the next empty row the you must identify the next empty row.
   lastrow = Sheets("Master").Range("A65536").End(xlUp).row + 1 'we add 1 so that it will be the next empty row
    Columns("A" & lastrow).Select '' paste to next empty row
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    ActiveWorkbook.Save
    ActiveWindow.Close
    Application.CutCopyMode = False
End Sub