| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a dynamic column range in macro?
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a dynamic column range in macro?
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" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a dynamic column range in macro?
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" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a dynamic column range in macro?
Sorry, you do need to change to a string....
ActiveWorkbook.Worksheets(CStr(Year(mydate))).Sele ct HTH, Bernie MS Excel MVP "will-d" wrote in message ... 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" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
copy a dynamic column range in macro?
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))).Sele ct HTH, Bernie MS Excel MVP "will-d" wrote in message ... 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" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A macro to copy & paste many rows (a range) to the next column .. | New Users to Excel | |||
| Copy and Paste Special Dynamic Range | Excel Programming | |||
| Copy via code from a dynamic range using offeset | Excel Programming | |||
| Dynamic range copy. | Excel Programming | |||
| Macro Syntax to copy and paste dynamic data based on one column | Excel Programming | |||
Linear Mode
