Copying data from one spreadsheet to another

[kellyhilde]

Okay, I have data for 400 employees in one spreadsheet. I am trying to move 4 fields of data to a second spreadsheet. But I have 11 rows for each employee in the 2nd spreadsheet.

How do I copy my formula so that it is the same for the first 11 rows and then moves down a row for the 12 row?

ie)
=A1
=A1
=A1
=A1
=A1
=A1
=A1
=A1
=A1
=A2
etc

 

[kellyhilde]

Looking at my example, I realize I forgot some vital information.

The first row will have all 4 fields of data, but row 2-11 will only have one piece of data.

How do I copy my formula so that it is the same for the first 11 rows and then moves down a row for the 12 row?

ie)
=A1, =A2, =A3, =A4
=A1
=A1
=A1
=A1
=A1
=A1
=A1
=A1
=B1, =B2, =B3, =B4
=B1
etc
<!-- / message -->

 

[MickG]

With your basic data in sheet1 starting "A2", this code will place you formulas in sheet2 starting "A2.

[COLOR="Navy"]Sub[/COLOR] MG21Oct03
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col     [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oFrm    [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
c = 2
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1")
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Sheets("Sheet2").Range("A" & c).Resize(, 4).Formula = "=" & .Name & "!" & Dn.Address & " : " & Dn.Offset(, 3).Address & ""
        Sheets("Sheet2").Range("A" & c + 1).Resize(10).Formula = "=" & .Name & "!" & Dn.Address & ""
        c = c + 11
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick