Stephen,

Is this what you want?

Sub copy()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim Firstcell As String
Dim cCols As Long
Firstcell = Sheets("Input").Cells(1, 1).Value
Lr = LastRow(Sheets(Firstcell)) + 1
cCols = Worksheets("Input").Cells(3,
Columns.Count).End(xlToLeft).Column
Set sourceRange = Sheets("Input").Range("A3").Resize(1, cCols)
Set destrange = Sheets(Firstcell).Rows(Lr).Resize(1, cCols)
destrange.Value = sourceRange.Value
End Sub


--

HTH

Bob Phillips

"Ste_uk" wrote in message
...
Hiya Board,

Below is a question I posted recently,
The response i received works perfectly for my request, But...
Unknown at the time I made an error in my post, What i should have
asked for was that the "range" of date in row 3
is transfered (Not the entire row)
I have tried experimenting with .....
("A3").CurrentRegion.copy in the code
but without success.
Any help would be greatly appreciated

Regards
Stephen.




"
I have a workbook that contains 10 worksheets,
Page one is for data input,

This is what I am trying to acheive.......
Copy data in row 3 and automatically move it to another worksheet...
dependant on what I enter in cell A1.
(Lets say the names of the other worksheets are Red White Blue...etc)

So if I enter "Red" in A1..
The data is sent to the chosen worksheet.
(data sent must append to the existing data) "



Sub copy()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Dim Firstcell As String
Firstcell = Sheets("Input").Cells(1, 1).Value
Lr = LastRow(Sheets(Firstcell)) + 1
Set sourceRange = Sheets("Input ").Rows("3:3")
Set destrange = Sheets(Firstcell).Rows(Lr). _
Resize(sourceRange.Rows.Count)
destrange.Value = sourceRange.Value
End Sub


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Regards Ron de Bruin