|I'm taking a guess here based on a test that I just ran.|
The new worksheets are being named based on the value in the cell below where CAN is found. If that value is a number, it might be a causing a problem.
This instruction is setting the name for the new worksheet:
wsName = acct.Offset(1, 0)
For example, if VBA see's the number 11 in the cell and names the new sheet 11, everything will be fine. However, when it attempts to write to that sheet via this code:
wsName will still be equal to 11 and VBA is going to look for the 11th sheet, not a sheet named 11.
Since I didn't have an 11th sheet when I ran the test, the code threw up an error on the line you posted.
My guess is that when VBA saw a number in your text file, it treated it as text, not a number. That means that the sheet was named with the text string e.g."11" not the number 11.
If that's the case, try adding this line as the first line under Sub AccountTabs():
Dim wsName As String
That would force VBA to set wsName to "11" (a text string) not 11 (a number).
If that's not the issue, you'll need to tell what is in the cell that is being used to name the new sheets.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.