I know there have been a lot of lookers (to date) and maybe too late for Sheila ... but this post is for late comers
You have declared possible cell addresses a Variants
ie, a1, a3, a4, s1 ... as Variants are interpreted by VBA as cell objects
- VBA assumes strings that look like a cell address are in fact Objects (of absolute reference) not variables
Best practice is to always to head up modules with "Option Explicit" so that you are forced to declare Variables by a specific data type.
- and never create variable names that can be misinterpreted by VBA as objects
eg, Sheet1, A1, B53... might be misinterpreted by VBA as literal objects on the active worksheet
If variables are not explicitly declared as specific data types you are setting yourself up to trip up.
Variable names are better constructed so that others can follow your intentions and for your own clarity in 2 or 3 years time.
Dim strName As String
Dim objOneCell as Object
Dim objShtRead As Sheet
Dim objShtWrite As Sheet
Dim intNumber As Integer
Dim a As Integer
Dim cc as Long