PDA

View Full Version : Worksheet macro



nsajeff
02-24-2009, 11:21 AM
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
02-24-2009, 11:23 AM
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
02-24-2009, 11:24 AM
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
02-24-2009, 01:04 PM
... or create an example file with made-up data. 3 invoices would suffice.

nsajeff
02-24-2009, 01:32 PM
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
02-24-2009, 01:44 PM
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

nsajeff
02-24-2009, 01:57 PM
xld, that appears to work perfectly. I'm genuinely impressed. Thanks very much for your time and help.

Bob Phillips
02-24-2009, 02:09 PM
Don't be, it was very easy with that format you have.

nsajeff
02-25-2009, 06:44 AM
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
02-25-2009, 06:55 AM
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
02-25-2009, 07:06 AM
xld, you're completely right, I solved that but now I'm being told, "Just name the worksheets 'P. #'.