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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
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 |