Solved Macro Problem using Excel file

Microsoft Office excel 2007
July 6, 2011 at 03:47:48
Specs: Windows XP

I have been running a tab-delimited text file through a macro tfor about a year, which puts the data for each accounting number into separate tabs. In the future, I will be receiving Excel files. The Excel files that I was provided with will not run through the macro. I have little experience with macros, but as far as I know, the macro searches for the word CAN in the file, retrieves the actual accounting number from the next line to label the tab, and pulls all the data into that tab until it comes to the word CAN again. The process then repeats. It creates about 40 tabs, which represents the accounting numbers in the file. When looking at the Excel file and the tab-delimited text file, they look the same. Do you have any idea why the Excel file would not run? Any help would be greatly appreciated!


See More: Macro Problem using Excel file

July 6, 2011 at 07:02:23
It might help if you posted the macro so we can see what it is doing.

Before you post the code, please click on the following line and read the instructions on how to post code in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 7, 2011 at 03:38:22
Sub AccountTabs()
Application.ScreenUpdating = False
'Determine last Row with Data
 lastRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
  With Sheets(1).Range("A1:A" & lastRow)
'Find each occurance of CAN
   Set acct = .Find("CAN", LookIn:=xlValues, lookat:=xlWhole)
     If Not acct Is Nothing Then
        firstAddress = acct.Address
           'Create Worksheet name with
           'Accounting Number from next cell
            wsName = acct.Offset(1, 0)
               'create new sheet
                Worksheets.Add After:=Worksheets(Worksheets.Count)
                'name new sheet
                Worksheets(Worksheets.Count).Name = wsName
                'Find end of data from Accouting Number and Copy it.
                .Range(acct.Address & ":P" & acct.End(xlDown).Row).Copy _
        'Find next CAN
         Set acct = .FindNext(acct)
        Loop While Not acct Is Nothing And acct.Address <> firstAddress
     End If
  End With
''Format all sheets to match Sheet 1
    'Copy cells in Sheet 1
    'Select all sheets except Sheet 1
     For Each ws In ActiveWorkbook.Sheets
      If ws.Index <> 1 Then ws.Select False
     Next ws
    'Paste Formats
      Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
    'Deselect Sheets
 Application.ScreenUpdating = False
End Sub

When I click the debug button, the following code is highlighted:

 .Range(acct.Address & ":P" & acct.End(xlDown).Row).Copy _

Report •

July 7, 2011 at 08:50:46
✔ Best Answer
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.

Report •

Related Solutions

July 8, 2011 at 12:03:35
That resolved the issue and the macro is running fine. Thank you so much for your help!

Report •

Report •

Ask Question