Transfer Data, Clear only values not formula - VBA

srikanth58

Board Regular
Joined
Jun 8, 2006
Messages
58
Hi,
I have two worksheets, DataEntry and DataSheet

'DataEntry' has two named ranges
A1:A10 ---- 'xxx' and A15:A25 ---- 'yyy'
The ranges have formula e along with user input figures.

Can you please help me with VBA,
With a command button I want to transfer range ('xxx' and 'yyy') VALUES to next available row starting from A1:A20 in 'DataSheet'.
Once data is transferred clear ONLY VALUES (not formula e) from named ranges in DataEntry sheet, ready for next data entry.

Now clear or delete is removing formula e too, and inconsistent data is entered in DataSheet.

Thanks in advance
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

stefankemp

Board Regular
Joined
Mar 11, 2010
Messages
136
Code:
sub cmdButton_Click()

nextrow = sheets("Datasheet").cells(sheets("Datasheet").rows.count,1).end(xlup).row+1

sheets("Dataentry").[xxx].copy
sheets("Datasheet").cells(nextrow,1).pastespecial paste:=xlpastevalues

sheets("Dataentry").[yyy].copy
sheets("Datasheet").cells(nextrow+11,1).pastespecial paste:=xlpastevalues

sheets("Dataentry").[xxx].specialcells(xlCellTypeConstants).clearcontents
sheets("Dataentry").[yyy].specialcells(xlCellTypeConstants).clearcontents

end sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,863
Latest member
stacy09
Top