VBA Copy paste from Sheet to Sheet

[CathalP1992]

Hi Everyone,
I have a task at the moment whereby I am trying to "Copy" a single cell containing real time data from "Sheet A" and paste that cell value at that specific moment in time into "Sheet B" on the next available row on in the designated column.



So that over time I will have a database of values of that parameter at that time and can manipulate how I require.

The VBA I have so far is this



Range("B2").Select
Sheets("MainSheet").Select
Range("D9").Select
Selection.Copy
Sheets("DatabaseSheet").Select
ActiveCell.Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste



The area I am having trouble is the final line.



ActiveSheet.Paste



pastes the copied cell and formula. All I want is the cell value at that time.

I have tried



ActiveSheet.PasteSpecial Paste:=xlPasteValues



but get an error.

Can anyone suggest a solution?

 

[HaHoBe]

Hi,

how about

Sheets("DatabaseSheet").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Sheets("MainSheet").Range("D9").Value

Ciao,
Holger

 

[CathalP1992]

Sheets("DatabaseSheet").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Sheets("MainSheet").Range("D9").Value

Thank you very much that worked perfectly.

 

[Alex Blakenburg]

Just in case you wanted to know what went wrong with your paste special, although Activesheet works for a straight paste and defaults to the Activecell, the paste special needs the actual range. So swap ActiveSheet for ActiveCell.

ActiveCell.PasteSpecial Paste:=xlPasteValues

 

[CathalP1992]

Just in case you wanted to know what went wrong with your paste special, although Activesheet works for a straight paste and defaults to the Activecell, the paste special needs the actual range. So swap ActiveSheet for ActiveCell.

ActiveCell.PasteSpecial Paste:=xlPasteValues

Cool thanks.