英文:
Object variable or With block variable not set - Run-time error '91'
问题
I have some simple copy and paste VBA code in Excel that was previously working. There may have been an update of some sort because now I keep getting the error in the title. The code is below:
y.Sheets("Data").Range("A1").PasteSpecial
Private Sub CommandButton1_Click()
Dim x As Workbook
Dim y As Workbook
Set x =
Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20-
%20Technical%20Business/Business%20Architects/Timesheet/Timesheet.xlsm")
Set y =
Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20-
%20Technical%20Business/Technical%20Coordinator/Timesheet%20Analysis/TimehsheetAnalysis.xlsm")
x.Sheets("Form1").Range("A2:U1000").Copy
Application.DisplayAlerts = False
y.Sheets("Data").Range("A1").PasteSpecial
x.Close
End Sub
Where am I going wrong?
英文:
I have some simple copy and paste VBA code in Excel that was previously working. There may have been a update of some sort because now I keep getting the error in the title. The code is below:
y.Sheets("Data").Range("A1").PasteSpecial
Private Sub CommandButton1_Click()
Dim x As Workbook
Dim y As Workbook
Set x =
Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20-
%20Technical%20Business/Business%20Architects/Timesheet/Timesheet.xlsm")
Set y =
Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20-
%20Technical%20Business/Technical%20Coordaintor/Timesheet%20Anaysis/TimehsheetAnalysis.xlsm")
x.Sheets("Form1").Range("A2:U1000").Copy
Application.DisplayAlerts = False
y.Sheets("Data").Range("A1").PasteSpecial
x.Close
End Sub
Where am I going wrong?
答案1
得分: 0
尝试以下代码：
Private Sub CommandButton1_Click()
Dim x As Workbook
Dim y As Workbook
Set x = Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20- %20Technical%20Business/Business%20Architects/Timesheet/Timesheet.xlsm")
Set y = Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20- %20Technical%20Business/Technical%20Coordaintor/Timesheet%20Anaysis/TimehsheetAnalysis.xlsm")
Application.DisplayAlerts = False
x.Worksheets("Form1").Range("A2:U1000").Copy
y.Worksheets("Data").Range("A1").PasteSpecial Paste:=xlPasteAll
x.Close
End Sub
英文:
Try the below.
Private Sub CommandButton1_Click()
Dim x As Workbook
Dim y As Workbook
Set x =
Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20-
%20Technical%20Business/Business%20Architects/Timesheet/Timesheet.xlsm")
Set y =
Workbooks.Open("https://nameofComp.sharepoint.com/sites/TechnicalBusiness/SharePoint%20-
%20Technical%20Business/Technical%20Coordaintor/Timesheet%20Anaysis/TimehsheetAnalysis.xlsm")
Application.DisplayAlerts = False
x.worksheets("Form1").Range("A2:U1000").Copy
y.worksheets("Data").Range("A1").PasteSpecial Paste:=xlPasteAll
x.Close
End Sub
答案2
得分: 0
Workbooks.Open 可能会导致问题，如果工作簿已经打开。
由于代码在工作簿 y 中，使用 ThisWorkbook：
Set y = ThisWorkbook
英文:
Workbooks.Open can cause issues if the workbook is already open.
Since the code is in workbook y, use ThisWorkbook:
Set y = ThisWorkbook
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论