Creating other Sheets from a Master Sheet

**papereditnow** · 07-16-2013, 09:11 AM

Hi, I am new to this forum, and this is my first post. However, I've used this forum many times to solve problems in Excel by searching past posts, so thank you everyone for all your help (even if you didn't know it!)

I am preparing a budget that lists over 300 parts. The parts are used by 7 different sites (cities) throughout the U.S. I am trying to use a master list of the parts to populate individual sheets of the sites with only the parts that they use.

After searching for a similar problem on this forum, I tried using the macro in the following post and adapting it to my needs, but I wasn't able to do so:

http://www.excelforum.com/excel-prog...-workbook.html

I appreciate any help. My file is attached. Budget.xlsm

**AB33** · 07-16-2013, 09:41 AM

Based on column A

**papereditnow** · 07-16-2013, 11:33 AM

AB33, Thanks for your response. However, I am trying to create sheets based on the cites (columns E-K), not the parts as you did in the file attached.

So ideally, the macro creates only 7 sheets (1 for each city) with the parts used by that city in the sheet. I hope this isn't confusing. Thanks for the help!

**AB33** · 07-16-2013, 12:40 PM

I do not know how the output looks, so please attach a sample desired output with the attached above.

**papereditnow** · 07-16-2013, 01:31 PM

I've attached the desired output. As you can see, the 7 city sheets are the output from the master sheet, "Parts." Once again, thanks for your help!

Budget Example 2.xlsm

**HaHoBe** · 07-16-2013, 01:35 PM

Hi, papereditnow,

give this code a chance:

Sub EF939490()
Dim lngCol As Long
Dim rngArea As Range
Dim lngLast As Long
Dim wsParts As Worksheet

Set wsParts = Sheets("Parts")
With wsParts
  lngLast = .Range("A" & Rows.Count).End(xlUp).Row
  .Range("A1:K" & lngLast).AutoFilter
  
  For lngCol = 5 To .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range("A1:K" & lngLast).AutoFilter Field:=lngCol, Criteria1:="<>"
    If .Range("A" & Rows.Count).End(xlUp).Row > 1 Then
      Set rngArea = .Range("A1:D" & lngLast)
      Set rngArea = Union(rngArea, .Range(.Cells(1, lngCol), .Cells(lngLast, lngCol)))
      rngArea.Copy
      Sheets.Add after:=Sheets(Sheets.Count)
      Range("A1").PasteSpecial
      ActiveSheet.Name = Range("E1").Value
      .Range("A1:K" & lngLast).AutoFilter
    End If
  Next lngCol
End With

Set wsParts = Nothing
End Sub

Ciao,
Holger

Creating other Sheets from a Master Sheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creating other Sheets from a Master Sheet

Re: Creating other Sheets from a Master Sheet

Re: Creating other Sheets from a Master Sheet

Re: Creating other Sheets from a Master Sheet

Re: Creating other Sheets from a Master Sheet

Re: Creating other Sheets from a Master Sheet

Thread Information

Users Browsing this Thread

Similar Threads

Master sheet to split sheets back to another master sheet

[SOLVED] [Urgent] Creating a Macro to Copy Time sheets Data of employees into Master Sheet

Creating a master sheet using data from other sheets in the workbook.

Creating report to run by date and pull info from other sheets to master sheet in one book

creating a master sheet from different .csv sheets

Bookmarks

Bookmarks

Posting Permissions