CODEWITHSUNDEEP
vbaexcel
Jack Armstrong
Jack Armstrong

Reputation: 1239

Subscript out of range Midcoded error

Usually with a subscript out of range error, something is misspelled. This time I can guarantee that it is not misspelled. I am declaring workbook and sheets names as variables to be used later for simple copy paste stuff. But when I define one of my sheets as Set main=wb.Sheets("Primary") I get the error, subscript out of range. I did't believe it, so I copied what I wrote and pasted that as the Sheet name. Same error. Therefore, their must be an issue with how I set up my Workbook/Worksheets. Any help much appreciated.

Sub copyPaste()

Dim wb As Workbook
Dim main As Sheets
Dim hypo As Sheets

Set wb = ThisWorkbook

Set main = wb.Sheets("Primary")
Set hypo = wb.Sheets("Hypo")

Application.CutCopyMode = False

wb.main.Range("D22:D46").Value = wb.main.Range("D22:D46").Value

End Sub

Upvotes: 0

Views: 48

Answers (1)

braX
braX

Reputation: 11755

You want Worksheet (singular) not Sheets (plural) (Sheets is a collection of worksheets)

Dim main As WorkSheet
Dim hypo As WorkSheet

and here you dont use the workbook object because it's already defined as part of the worksheet reference. Change it to this

main.Range("D22:D46").Value = main.Range("D22:D46").Value

Upvotes: 4

Related Questions