Worksheet macro

[nsajeff]

I am working with an excel file that contains nearly 200 invoices in one work sheet(each invoice occupies the same amount of space/ranges). I want to have a macro that takes the first 3 letters of a company name within B2 and create a new worksheet with that name then copy the invoice to that worksheet. I'm at a complete loss at how to do that.

Thanks for the help in advance.

[mdmackillop]

Welcome to VBAX
Can you post a sample showing the layout of your data? Use Manage Attachments in the Go Advanced section.
Regards
MD

[nsajeff]

Hi MD.

I'd love to post a sample, however, its government information(I work for an agency called the CRTC) and I'm not allowed to release it but I can certainly try and describe the layout if you'd like.

[Bob Phillips]

... or create an example file with made-up data. 3 invoices would suffice.

[nsajeff]

Alright, I have attached a sample. Thanks for the help so far. So, regarding the company name, that falls under the Undertaking name in my sample sheet.

[Bob Phillips]

[vba]

Public Sub CreateInvoices()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 3 To LastRow Step 39

Set sh = .Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
sh.Name = .Cells(i + 1, "B").Value
.Rows(i).Resize(39).Copy sh.Range("A1")
Next i
End With

End Sub
[/vba]

[nsajeff]

xld, that appears to work perfectly. I'm genuinely impressed. Thanks very much for your time and help.

[Bob Phillips]

Don't be, it was very easy with that format you have.

[nsajeff]

I'm running into a problem with that macro. It seems to create 2 worksheets(fake company 1 and 2) but then makes a "Sheet #" and stops.

[Bob Phillips]

Are all the invoice names unique, the macro assumes so as the worksheet name is based upon that. If not, what should happen when it meets a repeated name?

[nsajeff]

xld, you're completely right, I solved that but now I'm being told, "Just name the worksheets 'P. #'.