selecting month column

[DPingger]

Hello,

My downloaded column data header is formatted by mm/yyyy through 2014, a
total of 6400 rows.

Every month, I need to run a macro that would select last months column and
copy it to column DA1.

Help, please.

TIA

DPingger

 

[Don Guillett]

I can't visualize your file so, if desired send to my address below along
with this msg and a clear explanation.

 

[DPingger]

I apologize if I wasn't very clear.

06/2009 07/2009 08/2009 09/2009
55 200 1000
300 200 50
30 200
100
100 50 2

The file looks like this, over 6400 rows and runs till 2014. Every month,
regardless of the date of the month, I need to copy the previous month's
column and paste it in column DA and yes every month, column DA data will be
overwritten. The macro will be ran anytime during the month so it will use
the =now() and determine which column is last month.

TIA, Don.

Dpingger

 

[Patrick Molloy]

a worksheet formula would work for this

=OFFSET(A5,0,MATCH(TEXT(MONTH(TODAY())-1,"00") & "/"
&TEXT(TODAY(),"YYYY"),$B$1:$AA$1,FALSE))

put in DA1 and reolicate down

assumes columns with date headers are B1:AA1 ...change as required.
the MATCH returns the column, and then the offset returns the value for that
column for each row

 

[DPingger]

Thanks for the suggestion, Patrick. I do get a formula error, though. I'm
using 2007 would that make a difference?

Another question, how does A5 figure in the formula?

TIA

DPingger

 

[DPingger]

I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

 

[Patrick Molloy]

A5 was there because I'd dropped the formula into cells in DA but happened
to be in row 5 is all

did you know that in the formula bar, you can select a part of the formula,
like
TEXT(MONTH(TODAY())-1,"00")
then hit F9 to see what the calculated value is. DO NOT HIT ENTER ...use ESC
, as you do not want the calculated value entered into the cell

please check the calculation works as expected. a #N/A suggests that the
date created isn't in row 1

 

[DPingger]

The f9 works, shows the correct result for the month and year portion of the
formula, but the whole formula still yields a #N/A result.

I do have blank cells in a column but the non-blanks still shows #N/A.

TIA

DPingger

 

[Patrick Molloy]

so the formula returns 06/09
and you're sure that there's a column header like this in the first row?
If your column header 'looks' like 06/09 check that its not just formatted
like that, as the content is what MATCH will look for
so "06/09" will match "06/09" but not text(today(),"mm/yy") which also looks
like 06/09

 

[DPingger]

Thanks for the time, Patrick.

I've checked the column headers and they're all the first of the month
formatted as "mm/yyyy". That's probably why the formula shows a #N/A result.

Your suggestion however led me to an HLOOKUP that currently works but is not
as elegant as a single fomula. I ended up with =HLOOKUP((CN2),C:CJ,A2,TRUE)
where CN is populated with NOW() minus 1 month; works but not as polished.

I'd appreciate a cleaner approach.

Thanks

DPingger

 

[Don Guillett]

Does this do it?

Option Explicit
Sub CopyLastMonthtoColumn()
Dim mydate As String
Dim mc As Long
mydate = Format(DateSerial(Year(Date), _
Month(Date) - 1, 1), "mm/yyyy")
'MsgBox myDate
mc = Rows("1:1").Find(What:=mydate, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Column
'MsgBox mc
Columns(mc).Copy Range("da1")
End Sub

 

[DPingger]

Thanks, Don.

I will try it and let you know.

Does this do it?

Option Explicit
Sub CopyLastMonthtoColumn()
Dim mydate As String
Dim mc As Long
mydate = Format(DateSerial(Year(Date), _
Month(Date) - 1, 1), "mm/yyyy")
'MsgBox myDate
mc = Rows("1:1").Find(What:=mydate, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Column
'MsgBox mc
Columns(mc).Copy Range("da1")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[DPingger]

That is remarkably beautiful and elegant; eliminated a lot of my clutter.

Thank you very much, sir.

DPingger

 

[Don Guillett]

Glad it helped. It would be helpful, in the future, if you try to fully
explain your desires so we don't have to guess.