Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
currentregion | Excel Discussion (Misc queries) | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
Formula equivalent of CurrentRegion | Excel Worksheet Functions |