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.

Hi Kamranr?

---"K" wrote ...
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").MergeAre a


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.C ount)
Next
'---snip---


Greetings from Vienna, Austria,
-Robert Gelbmann-

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" wrote:

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.

On Aug 26, 9:58*am, Joel wrote:
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("A 3")
Workbooks.("book1.xls").Sheets("Sheet2").Range("B6 ") = _
* *Workbooks.("book2.xls").Sheets("Sheet1").Range("D 7")

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" wrote:
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.- Hide quoted text -

- Show quoted text -

Thanks Robert and Joel. You guies are fantastic

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

"Joel" wrote:

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" wrote:

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.