copy a dynamic column range in macro?

[will-d]

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.

 

[Bernie Deitrick]

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]

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]

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

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

HTH,
Bernie
MS Excel MVP

 

[will-d]

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