Macros in excel

Microsoft Excel 2007
January 4, 2010 at 05:04:49
Specs: Windows XP, 2.79 ghz, 3.48 gb of ram
Hi,

I need to create a worksheet for each row of data in a workbook. My master data is over 4500 lines in the first worksheet and I'd like to create a worksheet for each row until all rows have been added as a worksheet (i understand I may have to do this in multiple workbooks as there might not be enough space for 4500 worksheets). I'd also like to name each worksheet with a value in each row (it would be in column C of each row). Is there an easy way to accomplish this through macros? I'd love any help!

Tim



See More: Macros in excel

Report •


#1
January 4, 2010 at 05:58:29
No problem.

Report •

#2
January 4, 2010 at 06:01:02
i believe i am having trouble trouble reposting it - it is saying it has been posted already.

Report •

#3
January 4, 2010 at 06:06:05
Change the heading to something different than Macros in Excel. Try help with Excel.

Report •

Related Solutions

#4
January 4, 2010 at 06:54:14
Hi,

Make a copy/backup of your workbook before doing this, and test this on the copy.

With your workbook open, hit Alt + f11 (Alt key and function key 11 together).
This opens the VBA window.
On the left identify the name of your workbook (Preceded by VBAProject).
Double click the 'ThisWorkbook' item under MicrosoftExcelObjects.
In the Window on the right, enter this code:

Option Explicit
Sub AddSheets()
Dim rngUsed As Range
Dim rngCell As Range

On Error Resume Next

'set range in use on Sheet1
Set rngUsed = Worksheets("Sheet1").Range("C1", Worksheets("Sheet1") _
.Range("C65534").End(xlUp))
'add a new worksheet for each used cell in column C
For Each rngCell In rngUsed
    If rngCell.Text <> "" Then
        With ActiveWorkbook
            .Worksheets.Add After:=.Worksheets(.Worksheets.Count)
            .Worksheets(.Worksheets.Count).Name = rngCell.Text
        End With
    End If
Next rngCell
End Sub

If your worksheet with all the names is not called Sheet1, replace Sheet1 in the code with the name used.

Make sure there are no duplicate names in column C.

Click anywhere in the code after Sub AddSheets()
Click f5
This should create the new sheets.

I can't remember how many sheets Excel 2007 allows, but I think is is limited by available memory - so I have not included any code to open new workbooks.

Give it a try and see if it opens all 4500 sheets.

The On Error resume next line is to allow the process to continue even if a name in column C is not a legal Worksheet name, including duplicate names.

Regards

Humar
PS I tested this with 4600 names in column C and it worked. Note that Saving the workbook then takes ages! I thought Excel might have crashed - but it hadn't - just very slow!


Report •

#5
January 5, 2010 at 07:01:41
Humar,

Thanks very much! I have not tried it, but I sincerely appreciate you getting back to me. Will let you know how it goes.

Thanks again,
Tim


Report •

#6
January 5, 2010 at 07:17:27
Humar,

This code is adding worksheets, with the correct titles, but I also need the data in each row to be added to each worksheet as well. I.e. in my master worksheet i have data in columns A - H. I'd like to create a worksheet for each row with the name being the value in column C. However, I also need the data from Columns A - H (on the master) put onto the correct worksheet( i.e. i need the worksheet with the name from the master file C4 to also present the other data in row 4 (columns A-H)). Is it difficult to add that portion to the code?

Thanks,
Tim


Report •

#7
January 5, 2010 at 08:51:42
Hi,

Here are two alternative lines of code that can be added after the Worksheet is named,
i.e., after: .Worksheets(.Worksheets.Count).Name = rngCell.Text

One copies the relevant row always to row 1 in the new worksheets. The other copies the relevant row to the same row in the new worksheet. So if the new worksheet is named after the data in C10, the new worksheet will have the data in row 10.

'copy row of data to row 1 in new worksheet
rngCell.EntireRow.Copy Destination:=Worksheets(.Worksheets.Count).Range("A1")

'copy row of data to same row in new worksheet
rngCell.EntireRow.Copy Destination:=Worksheets(.Worksheets.Count).Range("A1") _
            .Offset(rngCell.Row - 1, 0)

Note that in the second option the code is split onto two lines using the continuation character '_'. You can copy 'as is' and it should work, or remove the '_' and bring it back to one line.

Hope that one of these will work.
Note that for ease of programming I have copied the whole row of data from the Master. If it is necessary only to copy columns A-H let me know and I can modify further.

Regards


Report •

#8
January 5, 2010 at 09:16:00
Humar,

The first line works close to how i want it. There are only certain columns (i used A - H for simplicity) that I need in each workbook. Therefore, if its no big deal, it looks like the "EntireRow.Copy" part of your code line needs to be modified to accomplish this (I'm starting to catch on!). Also, Each worksheet has to look the same (formatting, etc). Is there a way for the macro to look at a sample worksheet, then create all the other worksheets (per your original code and the description in one of the columns in the master), and then add the relevant data to each worksheet? I should have mentioned that it is necessary for each worksheet to have the same appearance / formatting (and again, I do have the sample/template). Maybe instead of the macro creating the worksheet, I can create the worksheet then have the macro name (or title) the worksheet as we discussed and then have the macro pull the relevant data to each worksheet (as per the 2nd line of code)?? Would that be easier or would it be easier for the macro to create the worksheet per the template, name it, then add the relevant data?


Report •

#9
January 6, 2010 at 09:25:35
If my previous post is too complicated, i think i will find what i need if I put it this way. if I have 1 master worksheet and then 10 additional worksheets that I need to pull a line of data into from the master, what is an easy macro to write for that (with a loop feature?)? In other words, I need to pull data from row 2 of the master worksheet into the 2nd worksheet, and from row 3 of the master worksheet into the 3rd worksheet, and so on.

Report •

#10
January 6, 2010 at 10:08:39
Hi,

1. Can you say specifically which cells are to be copied. You say you mentioned A to H for simplicity.

2a. Please confirm that the data to be copied will go into Row 1 of each new worksheet. For example the worksheet created and named after C3 will contain data from row 3 in the Master, but this data will be placed in Row 1 of the new worksheet.

2b. If data from the relevant row is to be copied, it is possible to place it in a variety of different cells on different rows if required. If A3 goes to B4 and B3 to C11 etc. etc. thats OK, but I need the exact cells.

3. If there is a template with the required formatting (column widths, headings, fonts, colors etc.) it is easy to format each new sheet to match.
What is the name of the Template or Workbook/worksheet with the required formats. Also is there fixed text to be copied.

Regards


Report •

#11
January 6, 2010 at 10:42:48
Hi,

1. There are 7 columns from the master worksheet that I need copied in each specific worksheet. Since I think I can just organize the data in the master worksheet however I need it, for now, I need data in columns A through I copied into Row 11 of each worksheet as follows: Columns A-B (2 columns) and then E-I (5 columns) for each worksheet. Please let me know if this does not make sense and I can rephrase - althought its probably as basic as you think it is.

2. The data to be copied will not go into Row 1. I believe I already figured out from your previous macro how to change the destination row (just changed Range("A1") to Range("A11")). Again, per above, need it to go into Row 11 of each worksheet.

3. I will call the template/workbook simply "Template". There is fixed text within this template that must be copied as well as formulas and shading in certain cells. I think I understand you will just refer to this "Template" in your macro?

Thanks Again,
Tim


Report •

#12
January 6, 2010 at 13:20:13
Hi,

I have modified the code.

It now creates the new worksheets by copying Sheet1 in Template.xls.
Template.xls must be open before you start the macro (you could add a line to open it, but as I didn't know the path to it, I didn't add this feature.)

Make sure you are in the Master workbook before starting the Macro.
As there are two workbooks open it is easy for the Macro to start adding worksheets to the wrong workbook.

You will see that there is now a line where the name of the active workbook is obtained:

'get the Active workbook name
strActWB = ActiveWorkbook.Name

If the wrong workbook is active this will get the wrong name.
If the master workbook always has the same name you could hardcode it in here:
'get the Active workbook name
strActWB = "MasterData.xls"

You can change the Template file name if required, it only appears once in the code, and note that I have assumed that the 'template' is Sheet1 in this workbook. Change if necessary.

As the new worksheets are formatted, all the data copied to row 11 is copied using PasteSpecial - Values, so that any formatting of cells on row 11 is not removed. As a result the all in one Copy - Destination: line is now two separate lines.

Here is the revised code:

Option Explicit
Sub AddSheets()
Dim rngUsed As Range
Dim rngCell As Range
Dim strActWB As String

On Error Resume Next

'get the Active workbook name
strActWB = ActiveWorkbook.Name
'set range in use on Sheet1
Set rngUsed = Worksheets("Sheet1").Range("C1", Worksheets("Sheet1") _
    .Range("C65534").End(xlUp))
'add a new worksheet for each used cell in column C
For Each rngCell In rngUsed
    If rngCell.Text <> "" Then
        With Workbooks(strActWB)
            'insert new worksheet by copying Template.xls Sheet1
            Workbooks("Template.xls").Worksheets("Sheet1").Copy After:= _
                Workbooks(strActWB).Worksheets(Workbooks(strActWB).Worksheets.Count)
            'name the new worksheet from data in column C
            .Worksheets(.Worksheets.Count).Name = rngCell.Text
            'copy columns A & B
            rngCell.Offset(0, -2).Resize(1, 2).Copy
            Worksheets(.Worksheets.Count).Range("A11").PasteSpecial _
                Paste:=(xlPasteValues)
            'copy columns E to I
            rngCell.Offset(0, 2).Resize(1, 5).Copy
            Worksheets(.Worksheets.Count).Range("E11").PasteSpecial _
                Paste:=(xlPasteValues)
        End With
    End If
Next rngCell
End Sub

Note that some lines have been split onto two lines for ease of viewing using the line continuation character "_"

When I tested this revised code, text, formating and formulas in the template were transferred to all the new worksheets.

Regards


Report •

Ask Question