copy cells with formulas and paste values

[Injinia]

Hi,


How could I use the below macro to copy cells with formulas and paste them as values?


Sub TransferData()

Dim LastRow As Integer


'Where is the last cell with data?

LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row


'Transfer data

Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A")

End Sub

 

[Luke M]

[pre]

Sub TransferData()
Dim LastRow As Integer

'Where is the last cell with data?
LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row

'Transfer data
Sheets("accuracy").Range("B23:L29").Copy
Worksheets("Sheet 1").Cells(LastRow + 1, "A").PasteSpecial xlPasteValues
End Sub

[/pre]

 

[Injinia]

Hi Luke


Thanks for your help. However now the macro doesn't run as it should. It copies the first round of values, then on running it again it copies over the previously copied values. Could you help so that it continously copies the values & number formats to the next blank cell on column A in sheet 1


-Injinia

 

[Luke M]

Hi Injinia,


I'm unable to duplicate your problem; macro is running as you describe on my machine. As there are only 3 command lines in the macro, I'm not sure what could be going wrong. Are there any other macros that might be interfering with this one? Perhaps if you try to step through the macro one line at a time (use F8 from within the VBE) you can see if the macro is properly figuring out what LastRow needs to be.

 

[Injinia]

Hi,


I am now reviewing this issue again... This macro gives me the following warning; "compile error syntax error". When I take away the last part of the macro ie (PasteSpecial xlPasteValues), it works fine.


What could be wrong? I need to past the values only..


-Injinia

 

[Luke M]

Looking over this,

[pre]

Sub TransferData()
Dim LastRow As Integer

'Where is the last cell with data?
LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row

'Transfer data
Sheets("accuracy").Range("B23:L29").Copy
Worksheets("Sheet 1").Cells(LastRow + 1, "A").PasteSpecial (xlPasteValues)
End Sub

[/pre]
Are the sheet names in the macro the exact same as in your workbook? Are there any merged cells involved?

 

[Injinia]

Hi Luke,


The sheet names are exactly the same. I do have 2 merged cells. Does that affect the functionality of the macro?


When I run the macro now I get this error message: "unable to get the paste special property of the range class"

 

[Luke M]

Merged cells often cause problems with VB because they mess up the cell sizes/formats/references. However, after doing some tests on my machine, it doesn't look like the merged cells should be affecting this.


That error doesn't make much sense...should only get that if you were copying something other than cells. Is there anything else unusual in the B23:L29 range on the accuracy sheet?


If nothing else, perhaps you could copy the macro you are using back to the forum? Only thing left I can think of is that some how it's not getting transferred correctly and causing a syntax error somewhere. =/

 

[Injinia]

Ninja,


I have dates, index & simple sum formulas in these cells. I can't figure out what else might be different in my sheets


This is what I have now:


Sub TransferData()

Dim LastRow As Integer


'Where is the last cell with data?(Data Archive)

LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row


'Transfer data

Sheets("accuracy").Range("B23:L29").Copy Worksheets("Sheet 1").Cells(LastRow + 1, "A").PasteSpecial(xlPasteValues)


End Sub


-Injinia

 

[Luke M]

Ah! Since we're doing a paste special, we don't want the two lines of code on same line. Otherwise, XL thinks we're trying to do a regular paste.

[pre]

Sub TransferData()
Dim LastRow As Integer

'Where is the last cell with data?(Data Archive)
LastRow = Worksheets("Sheet 1").Range("A65536").End(xlUp).Row

'Transfer data
Sheets("accuracy").Range("B23:L29").Copy
'Next line is separate
Worksheets("Sheet 1").Cells(LastRow + 1, "A").PasteSpecial(xlPasteValues)

'Added this line for cleanliness
Application.CutCopyMode = False
End Sub

[/pre]

 

[Injinia]

Excellent. works like a charm!!!


Thanks alot!


-Injinia

 

[Luke M]

Phew, glad we were able to finally get it solved. =)