Fill a column in a worksheet from a formula worksheet based on the date

[NadiaR]

I need help, I don’t know what I am doing wrong. What I want to do is copy a
column (from a worksheet that contains formulas) and paste the data to
another column on another worksheet (template with dates also) based on month
and year. I want this to happen untill there is no more data for that month.
Then next month I want it to take Feb 06 coulmn and do the same thing, and so
on for the following month. I want to create this macro so that when a person
clicks on a button that is linked to this macro…they update the template
sheet with the current month’s data. I can’t simply copy the whole sheet that
contains the formulas because I don’t want the previous months overwritten in
the template.

Below is an example of my data and what I have so far in VBA …and I know it
is incomplete and perhaps not the right VBA codes I need…please help.

Thank You in advance.
Nadia

moving left to right
Column AD
Inbound Nov-05 Dec-05 Jan-06 Feb-06
Oct-03 0 0 0
Nov-03 3 3 3
Dec-03 9 8 8
Jan-04 62 59 59
Feb-04 132 127 127
Mar-04 221 212 212
Apr-04 180 167 167


xrow = 5
xcolumn = AD
Cxrow2 = 1
Range("AD5") = ("Jan-06")
Range("AE5") = ("Feb-06")
Range("AF5") = ("Mar-06")

Do Until Range("xcolumn" & 6) = ""
Sheets("Reporting month").Select
If Range("L1") = "Jan-06" Then

Range("AD6:AD44").Select
Selection.Copy
Sheets("Data").Select
Range("AD6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

If Range("AE" & xrow) = "Feb-06" Then
Range("AE6:AE44").Select
Selection.Copy
Sheets("Data").Select
Range("AE6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End If
If Range("AF" & xrow) = "Mar-06" Then
Range("AF6:AF44").Select
Selection.Copy
Sheets("Data").Select
Range("AF6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
xcolumn = xcolumn + 1
Exit Do
End If


End Sub

 

[Don Guillett]

This should work for the current month from anywhere in the workbook. It
will not work for March if you wait until April but you can change the mm
variable.

Sub findfirstdate()
Set frng = Sheets("sourcesheet").Columns(1)
Set trng = Sheets("destinationsheet").Columns(1)
my = Year(Date)
mm = Month(Date)
fr = Application.Match(CDbl(DateSerial(my, mm, 1)), frng, 1) + 1
lr = Application.Match(CDbl(DateSerial(my, mm + 1, 1)), frng, 1) - 1
dr = Application.Match(CDbl(DateSerial(my, mm - 1, 31)), trng, 1) + 1
Sheets("sourcesheet").Rows(fr & ":" & lr). _
Copy Sheets("destinationsheet").Rows(dr)
End Sub

 

[NadiaR]

Hi Don,

do I apply what's below to my current VB? or do I delete everything I have
and use yours?

thanks so much

This should work for the current month from anywhere in the workbook. It
will not work for March if you wait until April but you can change the mm
variable.

Sub findfirstdate()
Set frng = Sheets("sourcesheet").Columns(1)
Set trng = Sheets("destinationsheet").Columns(1)
my = Year(Date)
mm = Month(Date)
fr = Application.Match(CDbl(DateSerial(my, mm, 1)), frng, 1) + 1
lr = Application.Match(CDbl(DateSerial(my, mm + 1, 1)), frng, 1) - 1
dr = Application.Match(CDbl(DateSerial(my, mm - 1, 31)), trng, 1) + 1
Sheets("sourcesheet").Rows(fr & ":" & lr). _
Copy Sheets("destinationsheet").Rows(dr)
End Sub

I need help, I don’t know what I am doing wrong. What I want to do is copy
a

[quoted text clipped - 73 lines]

End Sub

 

[NadiaR]

I tried this but it did not work, I need it to start at January...any
suggestions?

Hi Don,

do I apply what's below to my current VB? or do I delete everything I have
and use yours?

thanks so much

This should work for the current month from anywhere in the workbook. It
will not work for March if you wait until April but you can change the mm

[quoted text clipped - 17 lines]

 

[Don Guillett]

try

mm=1

--
Don Guillett
SalesAid Software
(e-mail address removed)

I tried this but it did not work, I need it to start at January...any
suggestions?

Hi Don,

do I apply what's below to my current VB? or do I delete everything I have
and use yours?

thanks so much

This should work for the current month from anywhere in the workbook. It
will not work for March if you wait until April but you can change the mm

[quoted text clipped - 17 lines]

End Sub