Either use the Worksheet .Name property or the Worksheet .CodeName property. Mixing and matching can only lead to trouble if they become 'unsynced'. In other words, if you ask for the next row to paste into from the worksheet codename Sheet1, then use the worksheet codename Sheet1 to identify the destination of your paste. There is nothing in your code that guarantees that the ActiveSheet property is the worksheet identified by Sheet1 codename, nor is there any guarantee that either is the worksheet with a name tab that says Sheet1.
- Open the Excel spreadsheet where you want to save the data and click the Data tab.
- In the Get External Data group, click From Text.
- Select the TXT or CSV file you want to convert and click Import.
- Select "Delimited".
- Click Next