#1
|
|||
|
|||
Debug error
I'm getting a debug error in the following code:Selection.PasteSpecial line
HTML Code:
Sheets("Master").Select Columns("A:O").Select Selection.Copy Workbooks.Open Filename:= _ "https://sharepoint.fngn.com/departments/ced/CommunicationsDelivery/PrintProd/Reports%20Library/MasterTableDBase2016.xlsx" _ , UpdateLinks:=3 Sheets("Master").Select Columns("A:O").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.Save ActiveWindow.Close Application.CutCopyMode = False |
#2
|
|||
|
|||
Hi,
This is a sample code. But it should work. However you should not copy the entire columns. You should get the "Last" used row and set the copy from "A1:O" & lastrow". You can do some research as to how to find the last used row. See if you can do this and if not get back too us. Code:
Sub teat() Sheets("Master").Activate Range("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 range("A1").Select''You do not need to select the entire range Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.Save ActiveWindow.Close Application.CutCopyMode = False End Sub |
#3
|
|||
|
|||
Debug Error
Hi Charlesdh,
I put in the code you suggested and got a debug error: Range("A1:O" & lastrow).Select |
#4
|
|||
|
|||
Hi,
Please re-read my post. I indicated that you need to "Search" for a code that will give you the "lastrow" of data. The following is a sample code for your project. There are other ways to do your code. But I kept my example to resemble your code. Code:
Sub teat() 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 srarting 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'' ''''' get lastrow again if you do not want the data to be copied over'' lastrow = Sheets("Master").Range("A65536").End(xlUp).Row + 1 Columns("A" & lastrow).Select ''''''''''''''''''''''''''''''''''' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.Save ActiveWindow.Close Application.CutCopyMode = False End Sub |
#5
|
|||
|
|||
Debug error
Still can't get it to work.
Here is the code I tried HTML Code:
Sub newcsvtosharepoint() ' ' newcsvtosharepoint Macro ' ' 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 srarting 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'' ''''' get lastrow again if you do not want the data to be copied over'' lastrow = Sheets("Master").Range("A65536").End(xlUp).row + 1 Columns("A" & lastrow).Select ''''''''''''''''''''''''''''''''''' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveWorkbook.Save ActiveWindow.Close Application.CutCopyMode = False End Sub |
#6
|
|||
|
|||
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 |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I review, and hopefully debug errors that are flashed by upon hitting "Send/Receive (F9)"? | brucemc777 | Outlook | 0 | 03-31-2016 10:57 AM |
How to create an error debug message | shilabrow | Excel Programming | 1 | 06-27-2014 07:30 PM |
VBA: How to debug.Print enumeration names? | tinfanide | Word VBA | 4 | 01-27-2012 01:41 AM |
Debug for macro run through button only when sheet protected | leahca | Excel Programming | 0 | 11-24-2011 04:47 AM |
Microsoft Office 2003 winword.exe Debug Error! abnormal prog | elo1bev | Word | 0 | 12-22-2005 10:15 AM |