paste VALUES ONLY of range to next empty row on another sheet

Have 2 sheets, source and destination. Source has data linked from othe
sheets (so there are formulas) and is updated frequently.  I need t
copy this data to the next empty record in the destination sheet (th
values only, NO formulas) one by one, that is whenever i update th
source sheet, i want to copy this to the destination sheet so i have 
permanent record of it.  I can get this to work for a single record (
row), but not for multiple rows, even when i set the range to multipl
rows (see comments in code)...any help appreciated!

'code modified from Ron de Bruin
(http://www.rondebruin.nl/copy1.htm#Row)
'function to find the last row
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

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
'end function to find last row

Sub copyRE_Values_PasteSpecial()
Dim REsourceRange As Range
Dim REdestRange As Range
Dim Lr As Long
Application.ScreenUpdating = False
Lr = LastRow(Sheets("Destination")) + 1
' even though the range is set from 1:6 below, all it will copy is th
1st range????    
Set REsourceRange = Sheets("Source").Range("1:6") '<<want to copy 
rows
Set REdestRange = Sheets("Destination").Rows(Lr)
' take the sourcerange value only (NOT the formula!) and use tha
for the destination range value
REdestRange.Value = REsourceRange.Value '<<think this may be th
problem
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Su

--
Message posted from http://www.ExcelForum.com

0
5/21/2004 8:57:27 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
478 Views

Similar Articles

[PageSpeed] 41

Look at the example that use resize to make both ranges
the same size.

http://www.rondebruin.nl/copy1.htm#Row

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"cdc01 >" <<cdc01.16mj9p@excelforum-nospam.com> wrote in message news:cdc01.16mj9p@excelforum-nospam.com...
> Have 2 sheets, source and destination. Source has data linked from other
> sheets (so there are formulas) and is updated frequently.  I need to
> copy this data to the next empty record in the destination sheet (the
> values only, NO formulas) one by one, that is whenever i update the
> source sheet, i want to copy this to the destination sheet so i have a
> permanent record of it.  I can get this to work for a single record (1
> row), but not for multiple rows, even when i set the range to multiple
> rows (see comments in code)...any help appreciated!
>
> 'code modified from Ron de Bruins
> (http://www.rondebruin.nl/copy1.htm#Row)
> 'function to find the last row
> 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
>
> Function Lastcol(sh As Worksheet)
> On Error Resume Next
> Lastcol = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
> 'end function to find last row
>
> Sub copyRE_Values_PasteSpecial()
> Dim REsourceRange As Range
> Dim REdestRange As Range
> Dim Lr As Long
> Application.ScreenUpdating = False
> Lr = LastRow(Sheets("Destination")) + 1
> ' even though the range is set from 1:6 below, all it will copy is the
> 1st range????
> Set REsourceRange = Sheets("Source").Range("1:6") '<<want to copy 6
> rows
> Set REdestRange = Sheets("Destination").Rows(Lr)
> ' take the sourcerange value only (NOT the formula!) and use that
> for the destination range value
> REdestRange.Value = REsourceRange.Value '<<think this may be the
> problem
> Application.CutCopyMode = False
> Application.ScreenUpdating = True
> End Sub
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3789)
5/21/2004 9:10:51 PM
Ron, you are the man! The method using resize worked like a charm! 
Thanks!

Chad!


---
Message posted from http://www.ExcelForum.com/

0
5/24/2004 7:35:22 PM
Reply:

Similar Artilces:

Set Bookmark Range results in Type Mismatch
I'm writing a VBA program which copies information from Excel into bookmarks in Word. I originally wrote it to be run from the Word document, but it makes more sense to have it in Excel so I'm migrating it over. The original code worked, and used ActiveDocument throughout, which I've replaced with myDoc. However, when run it gives the Type Mismatch error when I try to set the bookmark range and I can't figure out why. myDoc is defined and works well in other places (for instance, if you add the line: msgbox myDoc.Bookmarks(BookmarkToUse) into this sub it corre...

need to create a list box based on the selection of another list
I've done this in excel using =indirect in the list box. need to repeat in acces.. did have a response about a week ago, but cannot trace the link someone gave me. sorry to have to ask again, but it looked exatly what i wanted - had a new computer at work and they lost all my stored data... thanks what's your question ? -- Wayne Manchester, England. "Gover" wrote: > I've done this in excel using =indirect in the list box. need to repeat in > acces.. did have a response about a week ago, but cannot trace the link > someone gave me. sorry to have t...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

How do I set up a chart to only chart values greater than 0
I have a chart in Excell that I would like to only have chart those items with a value of greater than 0 (zero). Not sure if there is a better, easier way than having manually manipulate the data. Let's say the values are in B1:B20 In C1 enter =IF(B1>0,B1,NA()) Now use column C for the chart; the displayed #N/A values will be ignored by the chart engine best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "debra49424" <debra49424@discussions.microsoft.com> wrote in message news:A513E053-4AD9-44FE-952E-E9A60F49...

Match values in more than one column
Hi I have three columns of data and wish to highlight the matches or change the format if there are any duplicates. i.e A B C Apple Banana Cheese Banana Orange Milk Chocolate Milk Water Biscuits Pear Bread Wafers Plum Banana So Banana would be highlighted in all, Milk in B and C. I assume I'd use conditional formatting, but the MATCH functio...

Text values to numeric values
Is there a formula to convert a text value in a cell to a numeri value? Thanks, Jayveeja -- jayveeja ----------------------------------------------------------------------- jayveejay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=504 View this thread: http://www.excelforum.com/showthread.php?threadid=39463 Good afternoon Jatveejay Yes there is. Say your numeric value is in A1, in B1 the formula: =VALUE(A1) will show the number as a value. If you wanted to convert a number in its cell from a text number to a value number, ie., not use a formula in a di...

Need null values for several fields in db
I have an equipment inventory db. I need to run a query that would give me the records that contain null values in several of the fields. I have run a successful query to return records that have a null value in one field but don't know how to combine so that query returns records that also have null values in other fields or any other fields. Please advise -- dmp You join the seperate queries with a UNION. SELECT * From YourTable WHERE YourField Is NULL UNION SELECT * From YourTable WHERE YourField2 Is NULL UNION SELECT * From YourTable WHERE YourField3 Is NUL...

Adding summaries, rows and pre-population
I have a worksheet where the user can click a button marked 'Create Activity'. This spawns an Activity worksheet with the naming convention 'Activity (n)'. The user can click this as many times as they like, but must click it at least once. Problem #1: For each Activity sheet that they have created I would like to be able to copy a specific range e.g. A15:J28 on a 'Summary' worksheet and paste it at A30 on the 'Summary' worksheet and so on. So the 'Summary' worksheet just gets longer and longer if the user has created more activities. Problem # ...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Conditional format
Hi Conditional format Is there a way to get a cell's format determined by the format of a different cell?! e.g. Could one get an entire row market up in say bold red if one cell in that row was say less than 100 With thanks Ship Shiperton Henethe Hi Shiperton yep ) select the rows that you want to apply the conditional formatting to, ensuring that the first row of this group is the first row at the top of the screen (it gets confused sometimes if it isn't) the following example assums you've selected from row 2 onwards and you want the row to turn red if ...

Sharing a sub-folder of another's inbox
I can access another inbox with no trouble. I need to access a sub-folder of this inbox - I have permission for this but cannot find how to access. Is it possible? The owner of the mailbox needs to assign the appropriate permissions at the folder level, and grant you at least "reviewer" permissions at the root mailbox folder level. Then you'll need to add their mailbox to your Exchange server service properties so it will open in your folder list - it's in the advanced tab of the Exchange server properties. Denise wrote: > I can access another inbox with no trouble. ...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

SUMIF/AVERAGEIF with mulitle range and sum ranges
Im getting an #VALUE! when putting in this formula =AVERAGEIF(($B$6:$B$48,$B$70:$B$112,$B$134:$B$176,$B$198:$B$240),B262,($C$6:$C$48,$C$70:$C$112,$C$134:$C$176,$C$198:$C$240)) I know I'm using it wrong, please help! You can't use muliple range references like that with AVERAGEIF. Kind of long (but not as long as it could get!): =SUMPRODUCT(--(CHOOSE({1,2,3,4},B6:B48,B70:B112,B134:B176,B198:B240)=B262),CHOOSE({1,2,3,4},C6:C48,C70:C112,C134:C176,C198:C240))/SUMPRODUCT(--(CHOOSE({1,2,3,4},B6:B48,B70:B112,B134:B176,B198:B240)=B262)) -- Biff Microsoft Excel MVP &qu...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Giving a name to a row or column
I have a spreadsheet that has a bunch of rows of different length. I am doing a match to find a column I want - which is between my starting column and an ending column that is big enough to fit my longest row. But periodically I insert a new column in the front, making my longest row longer. I can solve this problem by making my end row huge. Maybe make it zz (what's the largest column possible?). But this offends my sensibilities a bit. What would be better is to somehow assign a variable name for my column that would be moved right when I add a new column. I s...

Row height #7
Hello all, Is there any way you can set the row heights, apart from doing i manually. What I am looking for is say the row height of row 1 to be set to th value in cell A1, row 2 height set to the value in B1 etc. Thanks very much in advance -- Message posted from http://www.ExcelForum.com One way would be to right click sheet tab>view code>copy/paste this>modify to suit>SAVE Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Rows(1).RowHeight = Target If Target.Address = "$B$1" Then Rows(2).RowHeight = Target End Sub -- D...

Setting the Focus to a Control if it is empty
Hey guys, I have a Form where if the user exits a Control without selecting an Employee Name, I would like to display a error box and then set the Focus back to the same Control. I have attched the Code I am using and get the Error Box but instead of setting the Focus to the same Control, moves on to the next Control in the TAb Order. Please help..... Private Sub EmployeeName_Exit(Cancel As Integer) Dim varEmployee As Variant If Not IsNull(Me.[EmployeeName]) Then varEmployee = DLookup("[EmployeeID]", "[EmployeeList]", "[EmployeeName] = '&q...

Look up multi values
I’m struggling figuring out how you would do the following? Work sheet data. A1 =25 B1 = 40 C D 1 25 30 2 25 40 3 27 41 4 28 42 What I want to do is lookup the values 25 & 40 (A1 & B1) from table C1 D4 Matching the value 25 in Column C & 40 in Column D & return the location in the table to Cell A2? If there is no matching value, were decimals come into play, i.e. 25.8, then return the next largest from column C with its parent value from D, Returned value = 2 or 3 if 25.5. Sounds simple but I get lost very quick! Any Help greatly appreciated. one way. This is an array...

Checking a Yes/No field for its value
I'm trying to set a value in a custom number field based on alternatives in two other fields, and this is what I have tried so far: IIf([Text18]="Business Activity",[Number10],[Number1]), where summary tasks may or may not meet the test condition. This works for tasks and milestones, apart from summary tasks where the relevant value is not copied in - it's set to 0. So how do I add in an additional test for summary tasks to set the value explicitly? Here's what I have tried so far ... IIf( [Summary], truepart, falsepart ) IIf( [Summary]=True, truepart, fa...

how to automatically insert row when i hit enter
I've got a list in a1 to a10, at the bottom of the list i have a sum of a1 to a10. when hitting enter in a10, how could i automatically insert a row so i can enter more data? try this right click sheet tab>view code>insert this>SAVE Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$10" Then Target.Rows.Insert End Sub -- Don Guillett SalesAid Software donaldb@281.com "Dano" <ganna@bellsouth.net> wrote in message news:u0P52wrrDHA.2364@TK2MSFTNGP09.phx.gbl... > I've got a list in a1 to a10, at the bottom of th...

Delete row in table by code
I have a table named Uploaded Documents that is created by a code that was developed by someone that used to work here. I have no coding experience so need some help. I need to add something to the end of his code that would delete any row where in the field SVBrand it contains either ## or @@ anywhere in that field. Any help would be apprectiated. Put this in the code and it will delete those rows: strSQL = "DELETE * FROM [Uploaded Documents] WHERE svBrand = '##' OR svBrand '@@';" CurrentDb.Execute(strSQL), dbFailOnError -- Dave Hargis, Microsoft A...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Numeric values in data area of the pivot tables
How can I bring a numeric data into the data area of a pivot table? I need to sort the numeric values in the numerical order. Any idea? ...

Parameters And Null Values
I have a query that I wish to return ALL values between 2 dates AND any records that the date happens to be null I have used the following: WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And [Forms]![ViewReports]![DateTo] Or (Schedule.DateSubmitted) Is Null)) And the result is ONLY the Null values. WHERE (((Schedule.DateSubmitted) Between [Forms]![ViewReports]![DateFrom] And [Forms]![ViewReports]![DateTo] And (Schedule.DateSubmitted) Is Null)) And the result is Nothing...an empty set. What do I need to do so that it will show me all records betwe...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...