Can someone help me understand why I get error 1004 from this script?
I am trying to find the first empty cell in colum A and select it so that I can paste additional data. It fails at the Range(KSB1_Hdr).Select
Dim KSB1_lastHdr As Integer
KSB1_lastHdr = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count + 1
Dim KSB1_Hdr As String
KSB1_Hdr = "A" & KSB1_lastHdr & ""
Selecting objects can be very buggy in Excel. My first guess would be that your code is in a worksheet's class module (not the "KSB1" worksheet) which means that your unqualified range property will refer to the range on that worksheet rather than the "KSB1" worksheet. The range object is by design strictly 2 dimensional in the Excel Object Model, so if the named range "KSB1_Hdr" is on the KSB1 worksheet, but your range property is implicitly qualified to a different worksheet, you'll get an error.
If I've successfully identified the problem there are 3 workarounds:
2. Leave the code the same but move your subroutine to a standard code module so the unqualified range property will reference the activesheet.
3. Best option!
The bottom line is that 99% of the time we don't need to select ranges - this normally just slows your code down and makes it prone to errors unless you code it very carefully. What do you intend to do with the range, we should be able to show you a better way of doing this?
I think it is likely you are exceeding the limit of the "Integer" Data Type by the number of rows. So to start with you should Type "KBS1_hdr" varible as a Long number vs the integer.
Now you can do something like this to work better:
Dim nlastHdr As Long
' Set the number of the first blank row
nlastHdr = Worksheets("KSB1").UsedRange.Rows.Count + 1
' Use the 'Cells' Method with the row/column value