Copy data from Single cell to Merged cell

[K]

Hi all, I have two Sheets. I want to copy data from the single cell
of Sheet1 to Merged cell of Sheet2. I want macro to copy Sheet1 cell
A3 value into Sheet2 cell B3 (which is Merged) and then Sheet1 cell A4
value into Sheet2 cell B6 (Sheet2 cell B6 is 3 cells below from cell
B3 as cells B3,B4 & B5 are Merged) and like this i want macro to
continue coping data from Sheet1 to Sheet2 until Sheet1 cell A40.
Basically macro should copy data from Sheet1 cells and paste it in 3
cells below from the target cell starting from cell B3 in Sheet2. I
hope I was able to explain my problem. Can any friend give me help on
this please. And also what will be the macro if i need to copy data
from Workbook1 to Workbook2 instead of Sheet1 to Sheet2.

 

[Robert Gelbmann]

Hi Kamranr?

Hi all, I have two Sheets. I want to copy data from the single cell
of Sheet1 to Merged cell of Sheet2. I want macro to [...]

MergeArea should solve the main problem:

Sheets("Sheet1").Range("A3").Copy _
Destination:=Sheets("Sheet2").Range("B3").MergeArea

Basically macro should copy data from Sheet1 cells and paste it in 3
cells below from the target cell starting from cell B3 in Sheet2. I
hope I was able to explain my problem. Can any friend give me help
on
this please. And also what will be the macro if i need to copy data
from Workbook1 to Workbook2 instead of Sheet1 to Sheet2.

'---snip---
Dim rngSource As Range
Dim rngDest As Range
Dim rng As Range

Set rngSource = Sheets("Sheet1").Range("A3:A6")
Set rngDest = Sheets("Sheet2").Range("B3")

For Each rng In rngSource.Cells
rng.Copy Destination:=rngDest.MergeArea
' Finding Cell below the used MergeArea:
Set rngDest = _
rngDest.Offset(RowOffset:=rngDest.MergeArea.Rows.Count)
Next
'---snip---


Greetings from Vienna, Austria,
-Robert Gelbmann-

 

[Joel]

You can't copy merged cells when they are differnt sizes.
You can move the values inside these merged cells one at
a time.

Sheets("Sheet2").Range("B3") = Sheets("Sheet1").Range("A3")
Sheets("Sheet2").Range("B6") = Sheets("Sheet1").Range("A4")

or
For Count = 3 to 40
Sheets("Sheet2").Range("B" & ((3 * count) + 3) = _
Sheets("Sheet1").Range("A" & Count)
next count


when moving between workbooks
Workbooks.("book1.xls").Sheets("Sheet2").Range("B3") = _
Workbooks.("book2.xls").Sheets("Sheet1").Range("A3")
Workbooks.("book1.xls").Sheets("Sheet2").Range("B6") = _
Workbooks.("book2.xls").Sheets("Sheet1").Range("D7")


It is better to set variable to make code easy to understand
Set source = Workbooks.("book2.xls").Sheets("Sheet1")
Set destination = Workbooks.("book1.xls").Sheets("Sheet2")

destination.Range("B3") = source.Range("A3")
destination.Range("B6") = source.Range("D7")

 

[K]

You can't copy merged cells when they are differnt sizes.
You can move the values inside these merged cells one at
a time.

Sheets("Sheet2").Range("B3") = Sheets("Sheet1").Range("A3")
Sheets("Sheet2").Range("B6") = Sheets("Sheet1").Range("A4")

or
For Count = 3 to 40
   Sheets("Sheet2").Range("B" & ((3 * count) + 3) = _
      Sheets("Sheet1").Range("A" & Count)
next count

when moving between workbooks
Workbooks.("book1.xls").Sheets("Sheet2").Range("B3") = _
   Workbooks.("book2.xls").Sheets("Sheet1").Range("A3")
Workbooks.("book1.xls").Sheets("Sheet2").Range("B6") = _
   Workbooks.("book2.xls").Sheets("Sheet1").Range("D7")

It is better to set variable to make code easy to understand
Set source = Workbooks.("book2.xls").Sheets("Sheet1")
Set destination = Workbooks.("book1.xls").Sheets("Sheet2")

destination.Range("B3") = source.Range("A3")
destination.Range("B6") = source.Range("D7")





- Show quoted text -

Thanks Robert and Joel. You guies are fantastic

 

[Joel]

I noticed I got my indexs a little wrong.

For Count = 0 to 37
Sheets("Sheet2").Range("B" & ((3 * count) + 3) = _
Sheets("Sheet1").Range("A" & (Count + 3))
next count