I have been looping through and copying data from some raw data files using VBA. For each iteration I have been using the following code to define the destination range:
Set destinationRange = WorksheetRange.Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1)
This works fine up until the 256'th column. After this, instead of setting the destination range as the next empty cell in row 2 it goes back to cell (2,2) as if the starting reference cell (.Cells(2, Columns.Count)) is within the occupied range and the .End method is going back to the beginning of the occupied range. But this shouldn't be the case, even during runtime I have verified that Columns.Count is equal to 16384 as it should be. So why is it that the code is seemingly using cell (2,256) as the result of .Cells(2, Columns.Count)? I have also tried adding a value into a cell many columns past 256 to try and remove any sort of behavior involving placeholder values for Columns.Count but this did not work either.
Appreciate any insight into this. It is also worth noting that no runtime errors are tripped, the copied data simply begins to overwrite in column 2 after reaching column 256.
"The worksheet that the code is running from is the current version of Excel but the source data files are xl2003".
Probably you're getting bitten by an implicit ActiveSheet:
WorksheetRange.Cells(2, Columns.Count).End(xlToLeft).Offset(0, 1)
Should be
WorksheetRange.Cells(2, WorksheetRange.Columns.Count).End(xlToLeft).Offset(0, 1)
Since the ActiveSheet (xl2003) only has 256 columns, End(xlToLeft) returns to column 1 after the 256th column.