0

I have a simple VBA Macro that basically copies cells from one Workbook to another. Now the thing is that another sheet was added to the Workbook where the macro runs. I simply would think that switching that Sheets(i).Select number to increase by one would do the trick. That turned out to simply not work. Any suggestion or help as to why this is happening would be greatly appreciated.

Sub BankerMacro()
'
' BankerMacro Macro
'

    Dim sForecastFile, sUploadFile
    Dim i As Integer
    i = 8
    
    Application.ScreenUpdating = False
    sForecastFile = ActiveWorkbook.Name
    Workbooks.Add
    sUploadFile = ActiveWorkbook.Name
    Range("A1") = "Company"
    Range("B1") = "Department"
    Range("C1") = "Location"
    Range("D1") = "Account"
    Range("E1") = "Intercompany"
    Range("F1") = "Amount"
    Range("G1") = "Fiscal Year"
    Range("H1") = "Fiscal Month"
    Range("I1") = "Scenario"
    Range("J1") = "Reporting"
    Range("K1") = "EndMarket"
    Range("L1") = "Project"
    Range("M1") = "Segment"
    Range("A2").Select
    Windows(sForecastFile).Activate
    
    For i = 8 To 8
        **Sheets(i).Select**
        Range("AC5").Select
        Do Until (Range("A" & ActiveCell.Row)) = "END"
            If Range("BX" & ActiveCell.Row) <> "" Then
                Range("BV" & ActiveCell.Row, "BY" & ActiveCell.Row).Copy
                Windows(sUploadFile).Activate
                Range(ActiveCell, ActiveCell.Offset(35, 0)).PasteSpecial xlPasteValues
                Range("E" & ActiveCell.Row, "E" & ActiveCell.Row + 35) = "ICDefault"
                Windows(sForecastFile).Activate
                Range(ActiveCell, ActiveCell.Offset(0, 35)).Copy
                Windows(sUploadFile).Activate
                Range("F" & ActiveCell.Row).PasteSpecial xlPasteValues, , , True
                Windows(sForecastFile).Activate
                Range("AC1:BL2").Copy
                Windows(sUploadFile).Activate
                Range("G" & ActiveCell.Row).PasteSpecial xlPasteValues, , , True
                Range("I" & ActiveCell.Row, "I" & ActiveCell.Row + 35) = sScenario
                Range("J" & ActiveCell.Row, "J" & ActiveCell.Row + 35) = "G/L Data (CC)"
                Windows(sForecastFile).Activate
                Range("BZ" & ActiveCell.Row, "CB" & ActiveCell.Row).Copy
                Windows(sUploadFile).Activate
                Range("K" & ActiveCell.Row, "M" & ActiveCell.Row + 35).PasteSpecial xlPasteValues
                Range("A1").End(xlDown).Offset(1, 0).Select
                Windows(sForecastFile).Activate
            End If
            ActiveCell.Offset(1, 0).Select
        Loop
    Next i
    Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
    Windows(sUploadFile).Activate
    ActiveWorkbook.SaveAs Path & "Final File Banker"
    Application.ScreenUpdating = True


End Sub

Please note the error occurs at Sheets(i).Select.

braX
  • 11,506
  • 5
  • 20
  • 33
SneakyKeys1025
  • 33
  • 4
  • 3
    It's considered a good idea to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. – cybernetic.nomad May 12 '23 at 17:00
  • Were you trying to highlight the code typing `**Sheets(i).Select**` or did you actually write the code like that? Anyway, your `i` cycle goes from 8 to 8. That shouldn't be the source of the error (unless of course you have less than 8 sheets) but it's still clearly a mistake. What does the error message report? – Evil Blue Monkey May 12 '23 at 19:20
  • Hello, I typed **Sheets(i).Select** only here, it isn't shown in the legit code. The error it prompts me with is: "Select method of Worksheet class failed" – SneakyKeys1025 May 12 '23 at 20:18
  • It's very strange because if I switch it to i = 7 to 7, it works perfectly fine. But I know there is 8 sheets. – SneakyKeys1025 May 12 '23 at 20:22
  • I have seen similar behaviour when more than one workbook has been open - you think you are running the code against Workbook A but it's actually running against Workbook B. To resolve the issue explicity state which Workbook you are using - e.g. use ThisWorkBook instead of ActiveWorkBook. You should also be doing the same with "Range" - explictily state which sheet that range is on – CHill60 May 15 '23 at 10:16
  • I ended up noticing it was choosing the wrong sheet in the end. Silly mistake on my end. Appreciate the help fellas. – SneakyKeys1025 May 20 '23 at 23:58

0 Answers0