getting content from 1 worksheet onto several at once

[travizwebb1]

Excel 2007,

I have Worksheet1 with 10 columns that start at A1-J1 and end at A20-J20. Each column contains different information (A: Address, B: Phone Number, C: Fax Number, etc.).

Here is what I need:

I need to distribute all of the information in each individual row in worksheet1 to it's own individual worksheet. For instance, I need everything from A1-J1 to populate into Worksheet2 in columns A1-J1, everything from A2-J1 to populate into worksheet3 in columns A1-J1, everything from A3-J3 to populate out to worksheet4 in columns A1-J1, etc.

I do not want to copy and paste each row into it's own worksheet, as I have to do this to several files, and many of them can contact up to 100 rows.

 

[dave3009]

I hope to clear up some ambiguity here....

I need to distribute all of the information in each individual row in worksheet1 to it's own individual worksheet.

I do not want to copy and paste each row into it's own worksheet,

I take it some sheets exist and you either want to tack the info onto the bottom of this or do you wish to overwrite the data?

 

[jeffreybrown]

Give this a try...

I see Dave has asked a few questions, but here is something that could be close to what you desire.

Sub TransferData()
    Dim wsSrc As Worksheet: Set wsSrc = Sheets("Worksheet1")
    Dim wsNew As Worksheet
    Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
    Dim wsName As String
    wsName = "Worksheet"
    Dim i As Long
    For i = 1 To LR
        Set wsNew = ActiveWorkbook.Worksheets.Add
        wsNew.Name = wsName & i + 1
        wsSrc.Range("A" & i & ":J" & i).Copy Destination:=wsNew.Range("A1")
    Next i
End Sub

 

[dave3009]

Not that it would make it much difference but seeing as the ranges are of equal dimension could you avoid copying

wsSrc.Range("A" & i & ":J" & i).Copy Destination:=wsNew.Range("A1")

could be

wsNew.Range("A1:J1") = wsSrc.Range("A" & i & ":J" & i)

 

[jeffreybrown]

Thanks Dave...good to know.