excel runtime error subscript out of range

July 28, 2011 at 20:44:28
Specs: Windows 7

I'm currently doing a macro in Office 2010 (Excel). I kept having a runtime error that said subscript out of range. My codes is to copy the data from B6 onwards from one sheet and paste it on another worksheet (Summary_Receipt_Fees) from B2 onwards. I've checked that all the name of the worksheets are spelled correctly.

Could anybody assist to debug the error?

The codes are as follows:

Sub CopyData()

Dim pasteRow As Long 'returns last row to be pasted

'Define Variables
Dim copyRow As Integer
Dim copyColARange As String
Dim copyContinue As Boolean
'Initialize variables
copyContinue = True
copyRow = 6
'Loop through all copied column B values until a blank cell is found
While copyContinue = True
copyRow = copyRow + 1
copyColARange = "B" & CStr(copyRow)
'Found a blank cell, do not continue
If Len(Range(copyColARange).Value) = 0 Then
copyContinue = False
End If
'Copy data from columns B - H in Print Receipt Fees
Range("B6:H" & CStr(copyRow - 1)).Select
Worksheets("Print Receipt Fees").Range("B6:H" & CStr(copyRow - 1)).Copy

'Selects Summary Receipt Fees Worksheet, the runtime errors highlight here.

'Activates cells to make them into the active cell

'Search for last empty row in Summary_Receipt_Fees
pasteRow = Range("B65536").End(xlUp).Row
Worksheets("Summary_Receipt_Fees").Range("B" & CStr(pasteRow + 1)).PasteSpecial

End Sub


See More: excel runtime error subscript out of range

Report •

July 29, 2011 at 07:17:18
It appears that you are aware that if the Sheet name can't be found, the code will produce a "Subscript out of range" error.

So, even though you said that you checked the spelling, it seems from where I'm sitting that the Sheet name on the tab doesn't match the Sheet name used in the code. Maybe there's a leading or trailing space or maybe something else is going on.

Try this, which seemed to work for me:

1 - Highlight Summary_Receipt_Fees in your code and press Ctrl-c to copy it.
2 - Right-click the Sheet tab that's causing the problem, choose Rename
3 - Press Ctrl-v to paste the string from the code.

Now the strings should match exactly and the VBA problem should be solved.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 2, 2011 at 01:01:10
Thanks DerbyDad.. yes, a friend of mine took a look at my worksheet and spotted an extra space on my worksheet name..

Report •

Related Solutions

Ask Question