Cannot select/copy upper range in Excel

Dad_x6

Board Regular
Joined
Jan 15, 2013
Messages
78
I have a very large spreadsheet (850 columns and 5000 rows) that I am trying parse into smaller chunks and then send into Access.

I have a loop that cycles through the columns and sends 120 columns at a time to a separate workbook (via a strRange) variable. Then, the new workbook is imported by Access into it's own table (yes I know... That is W-A-Y too many columns for an Access table).

Anyway, the process works great through the 4th set, and then I start getting Run-time error 1004 when it tries to either copy or select the range QH1:VJ4791, or any range higher than that.

I have tried the following...

a) ActiveSheet.Range(strRange).Copy Destination:=wbHolder.Sheets(1).Range("B1")
b) ActiveSheet.Range(strRange).Copy
c) ActiveSheet.Range(strRange).Select

Same error everytime, unless the range is smaller letters. So for example the 4th round's range is MX1:QG4791, and that one works without difficulty.

Here is the code I am using for the copy range part (the importing to Access is handled by Access). I also included the Excel Column Letter function so you could see what I am doing.

Code:
Option Explicit

Public Sub AccessImport(intTable As Integer, c As Long)
    Dim wbHolder As Object
    Dim lngRC As Long
    Dim strRange As String

    lngRC = Sheets("Master").UsedRange.Rows.Count
    Set wbHolder = Workbooks("AT_Holder.xlsx")
    ThisWorkbook.Activate
    strRange = ConvertToLetter(c) & "1:" & ConvertToLetter(c + 119) & CStr(lngRC)
    ActiveSheet.Range(strRange).Copy Destination:=wbHolder.Sheets(1).Range("B1")
    wbHolder.Application.DisplayAlerts = False
    wbHolder.Save
    wbHolder.Application.DisplayAlerts = True
    Set wbHolder = Nothing
    
End Sub


Function ConvertToLetter(iCol As Long) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Dad_x6

Board Regular
Joined
Jan 15, 2013
Messages
78
Here is some new information. I attempted building strRange in a different way (strRange = "QH" & "1:" & "VJ" & "4791") just to see if it would work. And it DID! So something appears to be causing it with the ConvertToLetter calls I make. But why would that be an issue on the 5th round, when it is not on rounds 1 through 4?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,176
Messages
5,442,835
Members
405,201
Latest member
kashyap44

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top