Subscript out of range Midcoded error

0
votes

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
1
vbaexcel
Are you sure that you have "Primary" and not e.g. "Primary "? – John Coleman
I am positive that their are no spaces. – Jack Armstrong
Are you sure that they are in the same workbook? – John Coleman
Rebooted it. Works fine. ugh. – Jack Armstrong

1 Answers

4
votes

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