コードが完成しました。
シートに作った【開始ボタン】からはエラーなく、処理ができるのですが、、、
Microsoft Visual Vasic for Applicationsの「▶」ボタンからでは、
**【オブジェクト変数または with ブロック変数が設定されていません】**となってエラーになり、処理ができません。
上記文言で色々と解決方法を探ったのですが、難しくて、理解ができません。
また、エラーが起きた際に出る黄色のマークもこの場合は出ないので、どこがいけないのかも検討もつかず、困っています。
もし分かりましたらご教授頂けますと幸いです。
Option Explicit 'グローバル宣言------------------------------- Public i As Long Public j As Long Public ss As String Public sss As String Public str As String Public msg As String Public fileName As String Public wb As Workbook Public ws As Worksheet Public Ws_list00 As Worksheet Public Ws_list01 As Worksheet Public Ws_list02 As Worksheet Sub All() Application.ScreenUpdating = False 'バックアップを取る Set wb = Workbooks.Open(ThisWorkbook.Path & "\表.xlsx") fileName = ThisWorkbook.Path & "/バックアップ/" & _ "表_バックアップ" & Format(Now(), "yyyy-mm-dd-hh-mm-ss") & ":バックアップ" & ".xlsx" wb.SaveAs fileName wb.Close '作業をする Workbooks.Open ThisWorkbook.Path & "\表.xlsx" Workbooks.Open ThisWorkbook.Path & "\list01.csv" Workbooks.Open ThisWorkbook.Path & "\list02.csv" Set Ws_list02 = Workbooks("list02.csv").Worksheets("list02") Set Ws_list01 = Workbooks("list01.csv").Worksheets("list01") Set Ws_list00 = Workbooks("表.xlsx").Worksheets("sheet1") Call Sheet2.list01_works Call Sheet2.list02_works 'ブックを閉じる Application.DisplayAlerts = False Workbooks("表1.csv").Close Workbooks("表2.csv").Close Workbooks("表.xlsx").Save Application.DisplayAlerts = True End Sub
別のシート(Sheet2)
Option Explicit 'グローバル宣言------------------------------- Public i As Long Public j As Long Public ss As String Public sss As String Public str As String Public msg As String Public fileName As String Public wb As Workbook Public ws As Worksheet Public Ws_list00 As Worksheet Public Ws_list01 As Worksheet Public Ws_list02 As Worksheet 'list01_works Sub list01_works() Application.ScreenUpdating = False '作業をする Workbooks.Open ThisWorkbook.Path & "\表.xlsx" Workbooks.Open ThisWorkbook.Path & "\list01_works.csv" Set Ws_list01 = Workbooks("list01_works.csv").Worksheets("list01_works") Set Ws_list00 = Workbooks("表.xlsx").Worksheets("sheet01") Call list01 'ブックを閉じる Application.DisplayAlerts = False Workbooks("list01_works.csv").Close Workbooks("表.xlsx").Save Workbooks("表.xlsx").Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub 'list02_works Sub list02_works() Application.ScreenUpdating = False '作業をする Workbooks.Open ThisWorkbook.Path & "\表.xlsx" Workbooks.Open ThisWorkbook.Path & "\list02.csv" Set Ws_list02 = Workbooks("list02.csv").Worksheets("list02") Set Ws_list00 = Workbooks("表.xlsx").Worksheets("sheet01") Call list02 'ブックを閉じる Application.DisplayAlerts = False Workbooks("list02.csv").Close Workbooks("表.xlsx").Save Workbooks("表.xlsx").Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub 'list01 Sub list01() For i = 50 To 785 For j = 1 To Ws_list01.Range("A" & Rows.Count).End(xlUp).Row sss = Ws_list00.Range("V" & i).Value ' 確認用 ss = Ws_list01.Range("A" & j).Value ' 確認用 If Ws_list00.Range("V" & i).Value = _ Ws_Ws_list01.Range("A" & j).Value Then Ws_list01.Range("B" & j).Copy Ws_list00.Range("W" & i).PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False Exit For End If Next j Next i End Sub