Solved: Copying Formula Results

[shaggles]

Is it possible to copy formula results rather than the formula? For example say cells A12 contains the formula =Sum(A2:A11) and the sum of those cells equals 120. How do I copy '120' (or whatever the sum) and paste that somewhere else with out reference to cell or formula?

[Kenneth Hobs]

Range("B1:D4").Value = Range("A12").Value

[shaggles]

Thanks. My next question is how would I copy an entire row from another workbook and paste it to the active workbook? One cell at a time?

[mdmackillop]

Workbooks("Book2.xls").Sheets(1).Rows(1).Copy Range("A1")

[k13r4n]

this will do it one row at a time, but there are better ways


Dim CopyRow as integer
dim PasteRow as integer

CopyRow = 1
PasteRow = 1

do until Worksheets("YourData").Range("A" & copyrow).Value = ""

Activesheets.Range("A" & PasteRow).Value = Worksheets("YourData").Range("A" & CopyRow).Value

CopyRow = CopyRow + 1
PasteRow = PasteRow +1

loop



you change CopyRow and PasteRow to the first row containg the data you want to copy form/ paste too and it will keep going untill it comes to an empty cell in the row you are copying values from


Kieran

[mdmackillop]

Hi Keiran,
Copying values is not quite the same as copying all. If that is what you need though, you can do all rows together, rather than one at a time.
Regards
MD


Sub CopyVals()
Dim Rw As Long
With Worksheets("YourData")
Rw = .Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Rows("1:" & Rw).Value = .Rows("1:" & Rw).Value
End With
End Sub

[k13r4n]

Hi Keiran,
Copying values is not quite the same as copying all. If that is what you need though, you can do all rows together, rather than one at a time.
Regards
MD


Sub CopyVals()
Dim Rw As Long
With Worksheets("YourData")
Rw = .Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Rows("1:" & Rw).Value = .Rows("1:" & Rw).Value
End With
End Sub



yup your right, but i thought from his second question he wanted a loop to do it one row at a time hence what i posted, i thought it was wierd.. :think: :dunno

[shaggles]

Thanks all. Sorry if my request was unclear. You've solved the problem inspite of the shortcomings of my post.

[shaggles]

OK. I spoke too soon. I'm getting a compile error. Here's the code:

Dim xlx As Object
Dim xlw As Object
Dim xlh As Object
Dim LR1 As String
Dim LR2 As String

Set xlx = CreateObject("excel.application")
Set xlw = xlx.workbooks.Open("C:\Book1.xls")
Set xlh = xlx.workbooks.Open("C:\Book2.xls")


LR1 = xlx.activesheet.usedrange.rows.Count - 1
LR2 = xlh.activesheet.usedrange.rows.Count - 1
workbooks(xlw).sheets(1).rows(1).copy Range("A" & LR1)
xlh.worksheets(1).Range("A" & LR2).insert shift:=xldown

xlw.Save
xlw.Close False
xlh.save
xlh.Close False
xlx.Quit
Set xlw = Nothing
Set xlx = Nothing

The error says 'Sub or Function not defined' and it's highlighting the word Range in this line:

workbooks(xlw).sheets(1).rows(1).copy Range("A" & LR1)

I'm running this from Access 2003 if that matters.

[mdmackillop]

When working between books, it's best to qualify Source and Target to ensure correct range is copied to correct destination.


Option Explicit
Sub test()
Dim xlw As Workbook
Dim xlh As Workbook
Dim LR1 As Long
Dim LR2 As Long
Set xlw = Workbooks.Open("C:\Book1.xls")
Set xlh = Workbooks.Open("C:\Book2.xls")


LR1 = xlw.ActiveSheet.UsedRange.Rows.Count - 1
LR2 = xlh.ActiveSheet.UsedRange.Rows.Count - 1

xlw.Sheets(1).Rows(1).Copy Range("A" & LR1)
xlh.Worksheets(1).Range("A" & LR2).Insert shift:=xlDown
xlw.Close True
xlh.Close False


End Sub

[shaggles]

Sorry. I don't understand what you mean by qualify source and target.

I'm still getting the compile error on Range in the line:

xlw.Sheets(1).Rows(1).Copy Range("A" & LR1)

Am I missing a library reference possibly?

[mdmackillop]

Set xlw = Workbooks.Open("C:\Book1.xls")
Set xlh = Workbooks.Open("C:\Book2.xls")

OK. You are setting variables to the books you open.

LR1 = xlw.ActiveSheet.UsedRange.Rows.Count - 1
LR2 = xlh.ActiveSheet.UsedRange.Rows.Count - 1


Potential problem. Activesheet will be that open when the book was last saved. Be specific
LR1 = xlw.Sheets(1).UsedRange.Rows.Count - 1
LR2 = xlh.sheets(1).UsedRange.Rows.Count - 1

xlw.Sheets(1).Rows(1).Copy Range("A" & LR1)

You are specific as to what to copy, but not where to paste. Something like
xlw.Sheets(1).Rows(1).Copy xlh.sheets(1).Range("A" & LR1)

xlh.Worksheets(1).Range("A" & LR2).Insert shift:=xlDown

This will insert one cell. I suspect you want a row.
xlh.Worksheets(1).Range("A" & LR2).EntireRow.Insert shift:=xlDown

I would suggest you create a subfolder to hold your files. Vista will not let users create files on the C root folder using code.