CurrentRegion.copy maybe?
 

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

CurrentRegion.copy maybe?
 

Hiya bob,

Thanks for the response..

tried the code but it is showing a syntax in

cCols = Worksheets("Input").Cells(3,
Columns.Count).End(xlToLeft).Column


any ideas?

regards
Stephen.

CurrentRegion.copy maybe?
 

Stephen,

What you are seeing as 2 lines of code should all be on the same line.
'Feature' of the newsgroups I am afraid. Join them together, and try it
again.

--

HTH

Bob Phillips

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

Thanks for the response..

tried the code but it is showing a syntax in

cCols = Worksheets("Input").Cells(3,
Columns.Count).End(xlToLeft).Column


any ideas?

regards
Stephen.

CurrentRegion.copy maybe?
 

Hiya Bob,

The code now works fine as requested,
But there is just one more issue I cannot work out....

I assume the code looks for the first empty row and inserts the data,
What I am trying to achieve is that the code looks for the first empty
cell in Column A and then inserts the data in that row.

I know I am pushing it (hopefully not too far) with this post but this
would definitely be the last question on this subject.

As always any help would be greatly appreciated,

Kind Regards
Stephen.

CurrentRegion.copy maybe?
 

Lr = LastRow(Sheets(Firstcell)) + 1

determines the last row, but I don't see this function posted anywhere, yet
you say the code works.

what is the function Lastrow you are using? That should be the source of
your problem.

--
Regards,
Tom Ogilvy


Ste_uk wrote in message
...
Hiya Bob,

The code now works fine as requested,
But there is just one more issue I cannot work out....

I assume the code looks for the first empty row and inserts the data,
What I am trying to achieve is that the code looks for the first empty
cell in Column A and then inserts the data in that row.

I know I am pushing it (hopefully not too far) with this post but this
would definitely be the last question on this subject.

As always any help would be greatly appreciated,

Kind Regards
Stephen.

CurrentRegion.copy maybe?
 

Hiya Stephen,

Okay, let's try this

Dim sourceRange As Range
Dim destrange As Range
Dim Firstcell As String
Dim cCols As Long, cRows As Long
Firstcell = Sheets("Input").Cells(1, 1).Value
cCols = Worksheets("Input").Cells(3, Columns.Count).End(xlToLeft).Column
cRows = Worksheets(Firstcell).Cells(Rows.Count, "A").End(xlUp).Row
If Not IsEmpty(Worksheets(Firstcell).Cells(cRows, "A").Value) Then
cRows = cRows + 1
End If
Set sourceRange = Sheets("Input").Range("A3").Resize(1, cCols)
sourceRange.copy Destination:=Worksheets(Firstcell).Cells(cRows, "A")

--

HTH

Bob Phillips

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

The code now works fine as requested,
But there is just one more issue I cannot work out....

I assume the code looks for the first empty row and inserts the data,
What I am trying to achieve is that the code looks for the first empty
cell in Column A and then inserts the data in that row.

I know I am pushing it (hopefully not too far) with this post but this
would definitely be the last question on this subject.

As always any help would be greatly appreciated,

Kind Regards
Stephen.