Copying data from first worksheet and populating other sheets

[Tinku]

Hi

I have a sp/sh which has 7 tabs. The first worksheet has the data
and based on data in Col 1, I want the data copied into the remaining 6
worksheets. But as this data is going to be updated frequently I want to delete the existing data from the 6 worksheets when the macro is run and data repopulated. I am attaching a sample file. please let me know if this doesnt make sense.

regards
Tinku

[Killian]

It makes sense, I think. Running this would do it. it relies on there being a blank row (2) between the headings and the source data but other than that, it should be ok for updating different amounts

Sub Main()
Dim rngSource As Range, rngHeadings As Range
Dim r As Long, s As Long
Set rngHeadings = Sheets("All").Range("A1:E1")
    For s = 2 To ActiveWorkbook.Sheets.Count
        ActiveWorkbook.Sheets(s).Select
        ActiveSheet.Cells.ClearContents
        ActiveSheet.Rows(1).Select
        rngHeadings.Copy
        Selection.Insert Shift:=xlDown
    Next
Set rngSource = Sheets("All").Range("A2").CurrentRegion
    For r = 3 To rngSource.Rows.Count
        rngSource.Rows(r).Copy
        Sheets(rngSource.Cells(r, 1).Value).Select
        ActiveSheet.Rows(3).Select
        Selection.Insert Shift:=xlDown
    Next
End Sub

[Tinku]

AWESOOOOOOOOME.. thanks so much Killian..
Have a on me.. thanks buddy.. can you just do me one more favour by explaining how this code works.. I am trying to learn VBA.

Regards
Tinku

[Killian]

You're welcome
I should have put some comments in the code... I think i must have been short on time for that one. But now you've asked... here's the code with lots of comments!
If you're looking to keep learning, you'll find lots of people happy to help you here at VBAX. Go through the posts and Knowledge base and see what you can pick up and most important, if you have any questions... just ask!
Good luck !

Sub Main()
'declare all the variables we want to use and the
    'data type for each. This is important - it tells the compiler
    'what everything is and how much memory to reserve for it
    Dim rngSource As Range, rngHeadings As Range
    Dim r As Long, s As Long
'set our "rngHeadings" varible by defining its sheet
    'and cell range. We can now just refer to it directly
    Set rngHeadings = Sheets("All").Range("A1:E1")
'ActiveWorkbook.Sheets.Count returns the number of worksheets in
    'activeworkbook's sheets collection. We could also have used
    '"For Each Sheet in ActiveWorkbook.Sheets... Next" to run our code
    'within the loop on each sheet and exculded the first sheet ("All").
    'this common method uses a counter variable (s) that is increacsed by 1
    'for each loop, and in each loop, we use it to refer to the sheet index
    'i.e. ActiveWorkbook.Sheets(s).Select
    For s = 2 To ActiveWorkbook.Sheets.Count    'loop of 2 to number of sheets
        ActiveWorkbook.Sheets(s).Select         'select sheet (index "s")
        ActiveSheet.Cells.ClearContents         'clear the sheet
        ActiveSheet.Rows(1).Select              'select row 1
        rngHeadings.Copy                        'copy the heading range we defined earlier
        Selection.Insert Shift:=xlDown          'with the selection (row 1) insert the copied row
    Next s  'increment s and start the loop over with new s value
    'when s exceeds ActiveWorkbook.Sheets.Count, the code will continue down
'set our "rngSource" varible by defining its sheet and cell range.
    'in this case we use the CurrentRegion method from cell A2 which expands
    'the range to include all contiguous data (equivalent to selecting the cell
    'top left in your data and pressing Crl+Shift+End)
    Set rngSource = Sheets("All").Range("A2").CurrentRegion
    'another incrementing For..Next loop, this time we use the variable r as
    'the row index row index
    For r = 3 To rngSource.Rows.Count               'loop 3 to number of row of data
        rngSource.Rows(r).Copy                      'copy source row "r"
        Sheets(rngSource.Cells(r, 1).Value).Select  'select the sheet named in col 1 of row "r"
        ActiveSheet.Rows(3).Select                  'select row 3 of that sheet
        Selection.Insert Shift:=xlDown              'insert copied row into that selection
    Next r 'increment r to process next row
End Sub

[Tinku]

Thank you so much Killian.. You are my EXCEL GURU
Expect to learn a lot from you.. thank you again.

Best Regards
Tinku