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. |
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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 05:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com