Using VBA Macro to copy data from 1 worksheet to another

[Bob Hilton]

I have 10 worksheets in the same format labled S1 thru S10.  I have another worksheet for data entry.  Each row contains data to be copied to one of the other 10 worksheets.  The destination is defined in a cell in that row, e.g. S5.

What is the syntax to paste the data to the desired worsheet?  Any help is greatly appreciated.

[#2]

Hi Bob

Does the input sheet get cleared out after the data is copied to the other sheets?

[#3]

Yes.

[#4]

Hopefully this will get you started in the right direction

There's all sorts of considerations to take into account but this is a basic starting point...

Sub CopyToSheets()
'

Dim c As Range
Dim strDestinationSheet As String

For Each c In Range("ColDestination")

    strDestinationSheet = c.Value

     c.EntireRow.Copy



     Sheets(strDestinationSheet).Select

      Range("A1").Select

     ActiveSheet.Paste

      Selection.EntireRow.Insert


Next c

End Sub

[#5]

Thank you Wyn. You got me unstuck and movinig in the right direction!