I have the following code works perfect when I have a dynamic row. The
variants Dayhour and k are calculated resultls from some conditions. This
piece of code copy the dynamic row from column C. The number of cells copied
is Dayhour.

ActiveSheet.Range("C" & k & ":C" & Dayhour + k - 1).Copy

Now things have changed and C is also need to change, becauseI have a data
source in that the sheets named 2005, 2006, 2007, etc and if it is in October
2006, C should be column K in sheet named 2006, and if it is November, C
should be replaced with column L in 2006.

If I input October 1st, 2006 into a variant called myDate:

Worksheets(Year(myDate)).Activate
DmdMonth = Month(myDate)

ActiveSheet.Range(??????? & k & : ?????? & Dayhour + k - 1).Copy

How do I input ????? in this condition?

Thank you very much.

Will,

ActiveSheet.Cells(k, DmdMonth +1).Resize(Dayhour).Copy

But there is no need for

Worksheets(Year(myDate)).Activate

You could use:

Worksheets(Year(myDate)).Cells(k, DmdMonth +1).Resize(Dayhour).Copy

HTH,
Bernie
MS Excel MVP


"will-d" <will-(E-Mail Removed)> wrote in message
news:EEEBC82A-F2F9-4D5C-AE53-(E-Mail Removed)...
>I have the following code works perfect when I have a dynamic row. The
> variants Dayhour and k are calculated resultls from some conditions. This
> piece of code copy the dynamic row from column C. The number of cells copied
> is Dayhour.
>
> ActiveSheet.Range("C" & k & ":C" & Dayhour + k - 1).Copy
>
> Now things have changed and C is also need to change, becauseI have a data
> source in that the sheets named 2005, 2006, 2007, etc and if it is in October
> 2006, C should be column K in sheet named 2006, and if it is November, C
> should be replaced with column L in 2006.
>
> If I input October 1st, 2006 into a variant called myDate:
>
> Worksheets(Year(myDate)).Activate
> DmdMonth = Month(myDate)
>
> ActiveSheet.Range(??????? & k & : ?????? & Dayhour + k - 1).Copy
>
> How do I input ????? in this condition?
>
> Thank you very much.

Thank you Bernie!

Before I can try your method, my code for other necessary operations to
activate or select the dynamic sheet name failed: the following code is not
working,

ActiveWorkbook.Worksheets(Year(myDate)).Select

or

ActiveWorkbook.Worksheets(Year(myDate)).Activate

can you please help?

Thank you, Will



"Bernie Deitrick" wrote:

> Will,
>
> ActiveSheet.Cells(k, DmdMonth +1).Resize(Dayhour).Copy
>
> But there is no need for
>
> Worksheets(Year(myDate)).Activate
>
> You could use:
>
> Worksheets(Year(myDate)).Cells(k, DmdMonth +1).Resize(Dayhour).Copy
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "will-d" <will-(E-Mail Removed)> wrote in message
> news:EEEBC82A-F2F9-4D5C-AE53-(E-Mail Removed)...
> >I have the following code works perfect when I have a dynamic row. The
> > variants Dayhour and k are calculated resultls from some conditions. This
> > piece of code copy the dynamic row from column C. The number of cells copied
> > is Dayhour.
> >
> > ActiveSheet.Range("C" & k & ":C" & Dayhour + k - 1).Copy
> >
> > Now things have changed and C is also need to change, becauseI have a data
> > source in that the sheets named 2005, 2006, 2007, etc and if it is in October
> > 2006, C should be column K in sheet named 2006, and if it is November, C
> > should be replaced with column L in 2006.
> >
> > If I input October 1st, 2006 into a variant called myDate:
> >
> > Worksheets(Year(myDate)).Activate
> > DmdMonth = Month(myDate)
> >
> > ActiveSheet.Range(??????? & k & : ?????? & Dayhour + k - 1).Copy
> >
> > How do I input ????? in this condition?
> >
> > Thank you very much.
>
>
>

Sorry, you do need to change to a string....

ActiveWorkbook.Worksheets(CStr(Year(mydate))).Select

HTH,
Bernie
MS Excel MVP


"will-d" <(E-Mail Removed)> wrote in message
news:45EAF516-0D9C-409A-87CA-(E-Mail Removed)...
> Thank you Bernie!
>
> Before I can try your method, my code for other necessary operations to
> activate or select the dynamic sheet name failed: the following code is not
> working,
>
> ActiveWorkbook.Worksheets(Year(myDate)).Select
>
> or
>
> ActiveWorkbook.Worksheets(Year(myDate)).Activate
>
> can you please help?
>
> Thank you, Will
>
>
>
> "Bernie Deitrick" wrote:
>
>> Will,
>>
>> ActiveSheet.Cells(k, DmdMonth +1).Resize(Dayhour).Copy
>>
>> But there is no need for
>>
>> Worksheets(Year(myDate)).Activate
>>
>> You could use:
>>
>> Worksheets(Year(myDate)).Cells(k, DmdMonth +1).Resize(Dayhour).Copy
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "will-d" <will-(E-Mail Removed)> wrote in message
>> news:EEEBC82A-F2F9-4D5C-AE53-(E-Mail Removed)...
>> >I have the following code works perfect when I have a dynamic row. The
>> > variants Dayhour and k are calculated resultls from some conditions. This
>> > piece of code copy the dynamic row from column C. The number of cells copied
>> > is Dayhour.
>> >
>> > ActiveSheet.Range("C" & k & ":C" & Dayhour + k - 1).Copy
>> >
>> > Now things have changed and C is also need to change, becauseI have a data
>> > source in that the sheets named 2005, 2006, 2007, etc and if it is in October
>> > 2006, C should be column K in sheet named 2006, and if it is November, C
>> > should be replaced with column L in 2006.
>> >
>> > If I input October 1st, 2006 into a variant called myDate:
>> >
>> > Worksheets(Year(myDate)).Activate
>> > DmdMonth = Month(myDate)
>> >
>> > ActiveSheet.Range(??????? & k & : ?????? & Dayhour + k - 1).Copy
>> >
>> > How do I input ????? in this condition?
>> >
>> > Thank you very much.
>>
>>
>>

Thank you Bernie, everything is working fine! You have a great day. )

"Bernie Deitrick" wrote:

> Sorry, you do need to change to a string....
>
> ActiveWorkbook.Worksheets(CStr(Year(mydate))).Select
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "will-d" <(E-Mail Removed)> wrote in message
> news:45EAF516-0D9C-409A-87CA-(E-Mail Removed)...
> > Thank you Bernie!
> >
> > Before I can try your method, my code for other necessary operations to
> > activate or select the dynamic sheet name failed: the following code is not
> > working,
> >
> > ActiveWorkbook.Worksheets(Year(myDate)).Select
> >
> > or
> >
> > ActiveWorkbook.Worksheets(Year(myDate)).Activate
> >
> > can you please help?
> >
> > Thank you, Will
> >
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> Will,
> >>
> >> ActiveSheet.Cells(k, DmdMonth +1).Resize(Dayhour).Copy
> >>
> >> But there is no need for
> >>
> >> Worksheets(Year(myDate)).Activate
> >>
> >> You could use:
> >>
> >> Worksheets(Year(myDate)).Cells(k, DmdMonth +1).Resize(Dayhour).Copy
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "will-d" <will-(E-Mail Removed)> wrote in message
> >> news:EEEBC82A-F2F9-4D5C-AE53-(E-Mail Removed)...
> >> >I have the following code works perfect when I have a dynamic row. The
> >> > variants Dayhour and k are calculated resultls from some conditions. This
> >> > piece of code copy the dynamic row from column C. The number of cells copied
> >> > is Dayhour.
> >> >
> >> > ActiveSheet.Range("C" & k & ":C" & Dayhour + k - 1).Copy
> >> >
> >> > Now things have changed and C is also need to change, becauseI have a data
> >> > source in that the sheets named 2005, 2006, 2007, etc and if it is in October
> >> > 2006, C should be column K in sheet named 2006, and if it is November, C
> >> > should be replaced with column L in 2006.
> >> >
> >> > If I input October 1st, 2006 into a variant called myDate:
> >> >
> >> > Worksheets(Year(myDate)).Activate
> >> > DmdMonth = Month(myDate)
> >> >
> >> > ActiveSheet.Range(??????? & k & : ?????? & Dayhour + k - 1).Copy
> >> >
> >> > How do I input ????? in this condition?
> >> >
> >> > Thank you very much.
> >>
> >>
> >>
>
>
>