: Range("A2:A13").Select
: Range(Selection, Selection.End(xlToRight)).Select
: Range("A2:S13").Select
: Range(Selection, Selection.End(xlToRight)).Select
: Selection.Copy
: Sheets("DMH").Select
: Range("A2").Select
: ActiveSheet.Paste

This looks like a recorded macro. It is not necessary to do
all that selecting. This does the same thing without leaving a
copy of the range  in the clipboard.

ActiveSheet.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A2")


: I Added this...
: 'Sheets("DMH").Select
: 'If ("d9") = "4" Then
: 'Sheets("DMH").Select
: 'Range("A63").Select
: 'ActiveSheet.Paste
: End If
:
: What I want is if in the cell  range d2:D15 if one of the cells  has a
: value(month) let's say 4(will either be month(4) or null) then I want
: to copy  the same data  to another part  of the SS, (for 4 it would be
: A63.

Loop through each cell in the range and test its value separately.

Dim rngCell As Range

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If rngCell.Value = 4 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")

Next rngCell

End With

This works great..
I don't know if my previous message posted, but..
I added:
Sheets("DMH").Activate
because I am running the macro  from the "ALLDATA" sheet
It's seemed to work??
Ok, now, I figured I'll just copy  past the above code
change a few things around for another worksheet and the same would
work for that?
Wrong...
I get "cannot change part  of a merged cell"
On the next part I added  for the "DMR" worksheet..

ActiveSheet.Range("A14:ad25", Range("A14:ad25").End(xlToRight)).Copy _
Destination:=Worksheets("DMR").Range("A2")

Am I supposed to reset something??

: I don't know if my previous message posted, but..
: I added:
: Sheets("DMH").Activate
: because I am running the macro  from the "ALLDATA" sheet
: It's seemed to work??

Are you asking a question or just using question marks
instead of periods?


: Ok, now, I figured I'll just copy  past the above code change
: a few things around for another worksheet and the same would
: work for that?
: Wrong...
: I get "cannot change part  of a merged cell"

You might have merged cells  in the destination which Excel
won't let you overwrite. This is a feature, not bug. :) I was
able to reproduce the error in Excel by merging cells A1:C4 in
the destination sheet  and trying to copy cells A1:E2 from the
source sheet.

If you can't remove the merged cells manually, start a new
thread for programmatic solutions.


: On the next part I added  for the "DMR" worksheet..
:
: ActiveSheet.Range("A14:ad25", Range("A14:ad25").End(xlToRight)).Copy _
: Destination:=Worksheets("DMR").Range("A2")
:
: Am I supposed to reset something??

It looks like you have this code. It should copy the range
A14:ad25 plus extra contiguous stuff to the right of that range
from DMH to DMR. Is that what is happening? If not, explain what
is happening.

Sheets("DMH").Activate
ActiveSheet.Range("A14:ad25", Range("A14:ad25").End(xlToRight)).Copy _
Destination:=Worksheets("DMR").Range("A2")

Thanks, that works great.
But can I add the part  you wrote to the same macro?

I tried it like this:

ActiveSheet.Range("A2:ad13", Range("A2:ad13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A2")
Dim rngCell As Range

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")
'Copy to January
If rngCell.Value = 1 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A18")
'Copy to February
If rngCell.Value = 2 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A33")
'Copy to March
If rngCell.Value = 3 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A48")
'Copy to April
If rngCell.Value = 4 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")
'Copy to May
If rngCell.Value = 5 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A63")
'Copy to June
If rngCell.Value = 6 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A93")
'Copy to July
If rngCell.Value = 7 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A108")
'Copy to August
If rngCell.Value = 8 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")
'Copy to September
If rngCell.Value = 9 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A138")
'Copy to October
If rngCell.Value = 10 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A153")
'Copy to November
If rngCell.Value = 11 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A168")
'Copy to December
If rngCell.Value = 12 Then _
.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A183")

Next rngCell

End With

But get an error:
On this..

.Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")
I'm assuming it's because I'm on the ALLDATA sheet.
Becuase if I rum it from DMH sheet  it work fine.
I assume I have to name the sheet it's going to?

: But can I add the part  you wrote to the same macro?

You can do anything you wish. The question is: will it do what
you want it to do?

: I tried it like this:
:
: ActiveSheet.Range("A2:ad13", Range("A2:ad13").End(xlToRight)).Copy _
: Destination:=Worksheets("DMH").Range("A2")
: Dim rngCell As Range
:
: With Worksheets("DMH")
:
: For Each rngCell In .Range("d2:d15")
: 'Copy to January
: If rngCell.Value = 1 Then _
: .Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
: Destination:=.Range("A18")

[snip]
: 'Copy to December
: If rngCell.Value = 12 Then _
: .Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
: Destination:=.Range("A183")

You are thinking like a typist. Look for patterns. Think about
doing more with less. Avoid typing the same thing repeatedly. This
is untested, but ...

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then _
.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Next rngCell

End With

The first test (Int(rngCell.Value) = rngCell.Value) makes
certain we have an integer. The second test (rngCell.Value < 13)
makes certain the integer is not greater than 12. The third test
makes certain that the integer is not less than 1. If all tests
test true, the copy  happens.


: But get an error:
: On this..

You should have included the text of the error. Most languages
have hundreds of error messages. I do not know which one you
received and without your workbook I probably cannot reproduce the
error.



: .Range("A2:S13", Range("A2:S13").End(xlToRight)).Copy _
: Destination:=.Range("A123")
: I'm assuming it's because I'm on the ALLDATA sheet.
: Becuase if I rum it from DMH sheet  it work fine.
: I assume I have to name the sheet it's going to?

It's that second range. I think it is missing the leading
period. Try this.

With Worksheets("DMH")

.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A123")


Which is short for this.

Worksheets("DMH").Range("A2:S13", _
Worksheets("DMH").Range("A2:S13").End(xlToRight)).Copy _
Destination:=Worksheets("DMH").Range("A123")

Thanks...
I'm almost there...
I think I can setup this for each worksheet.
I tried with the first, and came up witha problem,
I used what you wrote:
With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then _
.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Next rngCell

End With

This works except, if there is more than one value in d2:d15(ie, 4 on
two records) it copies it twice.
I tried putting "exit sub" after Destination... line
but then get nothing..
Thanks Again...

: I used what you wrote:

But, did you understand it.


: This works except, if there is more than one value in d2:d15
: (ie, 4 on two records) it copies it twice.

Well, that's what you told it to do. The loop checks each
value in the range  d2:d15. To stop on duplicate values, you'll
need to find a way to test for duplicates. The first step
would be to clearly define your criteria.

Are two copies really a problem? Each copy  on April should
produce the same result.


: I tried putting "exit sub" after Destination... line
: but then get nothing..

You mean like this?

With Worksheets("DMH")

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then _
.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Exit Sub

Next rngCell

End With

Now the algorithm only checks cell  d2, then exits the
sub. Cells d3:d15 are never checked. There is no logic in there
to check for duplicate values.

I think you were trying to do this, but it would stop after
the first valid value is reached and I am not clear  whether that
is what you really want.

For Each rngCell In .Range("d2:d15")

If Int(rngCell.Value) = rngCell.Value _
And rngCell.Value < 13 _
And rngCell.Value > 0 Then

.Range("A2:S13", .Range("A2:S13").End(xlToRight)).Copy _
Destination:=.Range("A" & rngCell.Value * 15)

Exit Sub

End If

Next rngCell


Your next step should not be programming anything. It should
be to articulate what you want to have happen when a duplicate
value is hit.

Also, I spotted an error in my code. To match your original
algorithm, Destination should be set to this. This still assumes
that originally the destination for May in your code was
mistakenly set to that of April (A63 instead of A78).

Destination:=.Range("A" & rngCell.Value * 15 + 3)