How to activate a window without crashing?

[sharonng]

I want to activate a window to edit the content. However, when I use

'open Aggregated Files
Workbooks.Open FileName:= _
    "J:\recoding\report_002\P09P05\Aggregated Files.xlsx", UpdateLinks:=0

'open D001A
Workbooks.Open FileName:= _
    "J:\recoding\report_002\P09P05\List of RI match with D001A in 2021Q2 SEV.xlsx", UpdateLinks:=0
    
'copy RI from D001A
Windows("List of RI match with D001A in 2021Q2 SEV.xlsx").Activate
Range("a1:c1692").Copy
            
'paste new quarter content to old quarter file and close the new quarter file
Windows("Aggregated Files.xlsx").Activate
Range("A1").PasteSpecial Paste:=xlPasteValues
Windows("List of RI match with D001A in 2021Q2 SEV.xlsx").Close

Windows("Aggregated Files.xlsx").Close

the program keeps running forever. What can I do?

 

[dmt32]

Hi,
untested but see if update to to your code does what you want

Sub Sharonng()
    Dim FileNames(1 To 2)   As String
    Dim wbFiles             As Workbook, wbSEV As Workbook
  
    FileNames(1) = "J:\recoding\report_002\P09P05\Aggregated Files.xlsx"
    FileNames(2) = "J:\recoding\report_002\P09P05\List of RI match With D001A in 2021Q2 SEV.xlsx"
  
    Application.ScreenUpdating = False
    On Error GoTo myerror
    'open Aggregated Files
    Set wbFiles = Workbooks.Open(Filename:=FileNames(1), UpdateLinks:=0, ReadOnly:=False)
  
    'open D001A
    Set wbSEV = Workbooks.Open(Filename:=FileNames(2), UpdateLinks:=0, ReadOnly:=True)
  
    'copy RI from D001A
    wbSEV.Worksheets(1).Range("a1:c1692").Copy
  
    'paste new quarter content to old quarter file and close the new quarter file
    wbFiles.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
   
myerror:
 'clear clipboard
    Application.CutCopyMode = False

    'close & save if no error
    If Not wbFiles Is Nothing Then wbFiles.Close CBool(Err = 0)
    'close without saving
    If Not wbSEV Is Nothing Then wbSEV.Close False
  
    Application.ScreenUpdating = True
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[sharonng]

Hi,
untested but see if update to to your code does what you want

Sub Sharonng()
    Dim FileNames(1 To 2)   As String
    Dim wbFiles             As Workbook, wbSEV As Workbook
 
    FileNames(1) = "J:\recoding\report_002\P09P05\Aggregated Files.xlsx"
    FileNames(2) = "J:\recoding\report_002\P09P05\List of RI match With D001A in 2021Q2 SEV.xlsx"
 
    Application.ScreenUpdating = False
    On Error GoTo myerror
    'open Aggregated Files
    Set wbFiles = Workbooks.Open(Filename:=FileNames(1), UpdateLinks:=0, ReadOnly:=False)
 
    'open D001A
    Set wbSEV = Workbooks.Open(Filename:=FileNames(2), UpdateLinks:=0, ReadOnly:=True)
 
    'copy RI from D001A
    wbSEV.Worksheets(1).Range("a1:c1692").Copy
 
    'paste new quarter content to old quarter file and close the new quarter file
    wbFiles.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
  
myerror:
 'clear clipboard
    Application.CutCopyMode = False

    'close & save if no error
    If Not wbFiles Is Nothing Then wbFiles.Close CBool(Err = 0)
    'close without saving
    If Not wbSEV Is Nothing Then wbSEV.Close False
 
    Application.ScreenUpdating = True
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

Thank you. It works except for Application.CutCopyMode = False and Application.ScreenUpdating = True.