Hello everyone,

I am searching for help with this VBA problem in Excel 2003:

I have a worksheet (Sheet2) where I have dates in row 12 (let's say Jan. 07
to Dec. 10). In row 13 I have some values which I need to copy to worksheet
No. 8, row 8 .
But not the whole row 13, just 12 values beginning with today's month.

Example:
Find the values in Sheet2, row 13 which are associated with the months
Oct.09 untill Oct.10 in row 12. Then copy the values to Sheet 8, row 8,
columns D to P.

It's important that the code is some kind of dynamic so that it inserts the
values for Nov 09 - Nov. 10 next week.

I think you mean you have dates in column 12 ("L") and the values you
want copied in columns 13 ("M").

1) Are the dates in Order
2) What is the format of the Dates 1/1/09 or Dec 10, 2009? Your
posting had a period after the abbreviation of the month. Is the period
in the actual date on the worksheet.
3) What is the name of worksheet No. 8?
4) Where on sheet No. 8 do you want the data?
5) Do you want to copy both the date and value or just the value?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Hi Joel,

no, I have the dates in row 12 (at the moment C12:AT12) and the values are
below in row 13

1) The dates are in order. But some cells in row 12 are empty (for example
between Dec.09 and Jan.10) or sometimes there is a cell called for example
"Sum 09".

2) The format of the dates in the source sheet is dd.mm.yyyy. But in the
cells it is shown as "mmm. yy"

3) Worksheet No 8 is called "Scenarios"

4) In the fields D6 to P6

5) I only want to copy the values.
In my destination worksheet No. 8 I am already using a macro whicht inserts
today's month in cell D5 and adds the other months in the cells E5:P5.
Or if you can provide a code which combines both it would be also great.


I think you mean you have dates in column 12 ("L") and the values you
want copied in columns 13 ("M").

1) Are the dates in Order
2) What is the format of the Dates 1/1/09 or Dec 10, 2009? Your
posting had a period after the abbreviation of the month. Is the period
in the actual date on the worksheet.
3) What is the name of worksheet No. 8?
4) Where on sheet No. 8 do you want the data?
5) Do you want to copy both the date and value or just the value?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Try this


Sub MoveDates()

StartDate = DateValue("1/7/09")
EndDate = DateValue("12/12/09")

NewCol = 4 'column D
With Sheets("sheet2")
LastCol = .Cells(12, Columns.Count).End(xlToLeft).Column
For ColCount = 3 To LastCol
If IsDate(.Cells(12, ColCount)) Then
MyDate = .Cells(12, ColCount).Value
If MyDate = StartDate And _
MyDate <= EndDate Then

Data = .Cells(13, ColCount)
With Sheets("Scenarios")
..Cells(6, NewCol) = Data
NewCol = NewCol + 1
End With
End If

End If

Next ColCount

End With




End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

Thanks, it works, but not perfectly.

First of all: I defined the variables! ;-)

When I run the macro, it writes the value of Jul. 09 from my source sheet to
my destination sheet below Oct. 09.
And it ends at Mar. 10 with the value of Dec. 09.
Something should be wrong in the code?!?

I need the values of this month (Oct. 09) to Oct. 10.
And if I use the workbook next week, the macro should insert Nov. 09 to Nov.
10.


"joel" wrote:


Try this


Sub MoveDates()

StartDate = DateValue("1/7/09")
EndDate = DateValue("12/12/09")

NewCol = 4 'column D
With Sheets("sheet2")
LastCol = .Cells(12, Columns.Count).End(xlToLeft).Column
For ColCount = 3 To LastCol
If IsDate(.Cells(12, ColCount)) Then
MyDate = .Cells(12, ColCount).Value
If MyDate = StartDate And _
MyDate <= EndDate Then

Data = .Cells(13, ColCount)
With Sheets("Scenarios")
.Cells(6, NewCol) = Data
NewCol = NewCol + 1
End With
End If

End If

Next ColCount

End With




End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

Now I change the formula for 2 variables:
StartDate = Date
EndDate = DateAdd("yyyy", 1, Date)

Then it enters nearly the correct values in my sheet "Scenarios".
But it starts with the value of Nov. 09 in the cell below Oct. 09 and ends
in the cell below Sep. 10 with the value of Oct. 10.

Any suggestions?

"maywood" wrote:

Thanks, it works, but not perfectly.

First of all: I defined the variables! ;-)

When I run the macro, it writes the value of Jul. 09 from my source sheet to
my destination sheet below Oct. 09.
And it ends at Mar. 10 with the value of Dec. 09.
Something should be wrong in the code?!?

I need the values of this month (Oct. 09) to Oct. 10.
And if I use the workbook next week, the macro should insert Nov. 09 to Nov.
10.


"joel" wrote:


Try this


Sub MoveDates()

StartDate = DateValue("1/7/09")
EndDate = DateValue("12/12/09")

NewCol = 4 'column D
With Sheets("sheet2")
LastCol = .Cells(12, Columns.Count).End(xlToLeft).Column
For ColCount = 3 To LastCol
If IsDate(.Cells(12, ColCount)) Then
MyDate = .Cells(12, ColCount).Value
If MyDate = StartDate And _
MyDate <= EndDate Then

Data = .Cells(13, ColCount)
With Sheets("Scenarios")
.Cells(6, NewCol) = Data
NewCol = NewCol + 1
End With
End If

End If

Next ColCount

End With




End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824

.

On the sheet "Scenarios" where are the dates located so I can put the
correct data in the correct column?


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=147824