Automatically updating one sheet, when in another sheet 1

[Sandals]

Hi,

Can anyone help me? I have a control button on a sheet to which I need to attach a macro. The problem is that I don't quite know how to create this particular macro. This is how the button should work.

When clicked, the data entered on that sheet(e.g. Sheet1), should be copied and pasted to another sheet. Normally this would have been simple except that the sheet on which the data is to be pasted, depends on the data that is entered. For example, the data entered in one cell(in Sheet1), will determine whether the data is to be copied to Sheet2, Sheet3 or Sheet4. Thanks for your help.

 

[PJFry]

Just for some clarification: If the value in A1 = X, then paste it to sheet1. If the value in B1 = Y, then paste it to sheet2, and so on. Is this correct?

 

[01Patrik]

If so:

Dim MyCell, MySheet as String

MyCell = activesheet.[the variable cell ref].value

if MyCell = XXX then MySheet = [Sheet name]
if MyCell = YYY then MySheet = [Sheet name]
if MyCell = ZZZ then MySheet = [Sheet name]

if MySheet = "" then
msgbox("Cell XXX contains the wrong value!")
exit sub
end if

activesheet.cells.copy
sheets(MySheet).range("A1").paste

You can use pastespecial instead if you like, but then you may run into problems if your data contains merged cells, since the pastespecial method doesn't handle that very well.


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[Sandals]

Thanks Patrik! One more question. After the button is clicked, the data to be pasted (e.g. from Sheet1 to Sheet3) must be appended to the table in the Sheet3. (I need to append a row of data). How can I ensure that it is pasted just after the last row in Sheet3?

 

[01Patrik]

In the end of the previous code, instead of the two last lines, use something like this

activesheet.cells.copy

LastUsedRow = sheets(Mysheet).find(what:="*", searchdirection:=xlprevious).row

sheets(MySheet).cells(LastUsedRow,1).paste

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[01Patrik]

Sorry - forgot to add one row - must be:

sheets(MySheet).cells(LastUsedRow + 1 ,1).paste

BTW, the messagebox code should really be

msgbox("Cell " & activesheet.[the variable cell ref].address & " contains a faulty value!")

...where [the variable cell ref] in all cases should be a
range/cell reference, for instance

range("A1")

...or...

cell(1,1)

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.