PDA

View Full Version : [SOLVED] Copying data from first worksheet and populating other sheets



Tinku
04-01-2005, 02:15 PM
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
04-01-2005, 04:22 PM
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
04-02-2005, 08:55 PM
AWESOOOOOOOOME.. thanks so much Killian..:cloud9:
Have a :beerchug: 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
04-03-2005, 07:41 AM
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 ! :thumb


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
04-04-2005, 06:57 AM
Thank you so much Killian.. You are my EXCEL GURU :clap:
Expect to learn a lot from you.. thank you again.

Best Regards
Tinku