0

I partially created / recorded a macro to do the following:

  1. Create a temp file named vbsTest.xlsx
  2. Copy some data from the worksheet with the macro to vbsTest.xlxs

I get an "out of range error" when Windows("vbsTest.xlsx").Activate or Workbooks("vbsTest.xlsx").Activate is called to set focus to the temp file and copy in the necessary data.

I've searched and found nothing that resolves what is going on. *Note, both files are located in the same folder. Please help!

Sub Macro1()
'
' Macro1 Macro
   
'create new excel document to house post-processed Network ATC file
    'create the excel object
    Set objExcel = CreateObject("Excel.Application")

    'view the excel program and file, set to false to hide the whole process
    objExcel.Visible = True
    
    'save the new excel file (make sure to change the location) 'xls for 2003 or earlier
    Set objWorkbook = objExcel.Workbooks.Add
    objWorkbook.SaveAs "H:\vbsTest.xlsx"

'begin processing input file
    Range("B4").Select
    Selection.ShowDetail = True
    Sheets("Sheet1").Select
    Range("C4").Select
    Selection.ShowDetail = True
    Selection.Copy
    
    Workbooks("vbsTest.xlsx").Activate
    ActiveSheet.Paste
    Windows("Test doc.xlsx").Activate
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Workbooks("vbsTest.xlsx").Activate
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Windows("Test doc.xlsx").Activate
End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
J. S.
  • 19
  • 5
  • I guess you did not have the file open yet, hence the error "out of range". You can use `Workbooks.Open` to open the file first – Rosetta Jun 02 '21 at 17:12
  • 2
    Instead of using `Windows` and `Workbooks`, refer to `objWorkbook`: `objWorkbook.Sheets("Sheet1")` and so on. Also see [How to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jun 02 '21 at 17:12
  • @Rosetta, that worked, thank you. – J. S. Jun 02 '21 at 18:14
  • @BigBen, for some reason, calling the objWorkbook sheet was not successful. I appreciate both of your speedy inputs in helping me resolve this. – J. S. Jun 02 '21 at 18:15
  • @BigBen, no, no longer have an issue. What Rosetta suggested worked. From some reason, I am not seeing how to mark this as answered. – J. S. Jun 02 '21 at 18:51
  • You can only mark an answer accepted... @Rosetta has only posted a comment. – BigBen Jun 02 '21 at 18:53

1 Answers1

0

Calling Workbooks.Open for the target file resolved the issue.

(Answer provided in comments by @Rosetta)

J. S.
  • 19
  • 5