VBA CODING FOR MERGED CELLS

Question

is it possible to copy the value of a single cell in a sheet to a merged cell in another sheet?

Answer 1

thanks for your speedy reply. my problem is that i want to transfer the value in b6 in sheet 1 to a range a1:j1 in sheet 2. when i try the code msg is "run time error 1004 cannot change part of a merged cell."

Using what I posted originally as a model, each of the following formulas work for me (the bold D1 shown in the second formula could be any one of the cells in the range A1:J1 on Sheet2)...

Sheets("Sheet2").Range("A1").Value = Sheets("Sheet1").Range("B6").Value

Sheets("Sheet2").Range("D1").MergeArea.Value = Sheets("Sheet1").Range("B6").Value

Sheets("Sheet2").Range("A1:J1").Value = Sheets("Sheet1").Range("B6").Value

Do these work for you? If not, do you have Sheet2 protected?

Answer 2

when i try vba code i get the message: "run time error 1004 cannot change part of a merged cell."

Answer 3

thanks for your speedy reply. my problem is that i want to transfer the value in b6 in sheet 1 to a range a1:j1 in sheet 2. when i try the code msg is "run time error 1004 cannot change part of a merged cell."

Answer 4

is it possible to copy the value of a single cell in a sheet to a merged cell in another sheet?

If you mean you want to be able to specify any cell within the merged cells, you could do it this way... assume the merged cells are Sheet2!D4:F6 and you wanted to be able to specify any one of those cells in order for the value in, say, Sheet1!A1 to be copied into the merged cells, you could use this code line... let's say you wanted to specify E5, then

Sheets("Sheet2").Range("E5").MergeArea.Value = Sheets("Sheet1").Range("A1").Value

If you know the address for the top left cell of the merged cells, you can do a direct assignment like this...

Sheets("Sheet2").Range("D4").Value = Sheets("Sheet1").Range("A1").Value

If this is not what you meant, can you please expand your description so we can figure out what you actually want (before and after examples are always good to provide).

Answer 5

What happens when you try?

Gord