selecting month column
 

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

selecting month column
 

I can't visualize your file so, if desired send to my address below along
with this msg and a clear explanation.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DPingger" wrote in message
...
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

selecting month column
 

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

"Don Guillett" wrote:


I can't visualize your file so, if desired send to my address below along
with this msg and a clear explanation.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DPingger" wrote in message
...
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

selecting month column
 

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" wrote in message
...
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

selecting month column
 

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

"Patrick Molloy" wrote:

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" wrote in message
...
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

selecting month column
 

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" wrote in message
...
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" wrote:

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" wrote in message
...
I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

"DPingger" wrote:

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

"Patrick Molloy" wrote:

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" wrote in message
...
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

selecting month column
 

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

"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

"DPingger" wrote:

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

"Patrick Molloy" wrote:

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" wrote in message
...
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

selecting month column
 

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

"DPingger" wrote in message
...
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

"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

"DPingger" wrote:

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

"Patrick Molloy" wrote:

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" wrote in message
...
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

selecting month column
 

Thanks, Don.

I will try it and let you know.

"Don Guillett" wrote:

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

"DPingger" wrote in message
...
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

"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

"DPingger" wrote:

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

"Patrick Molloy" wrote:

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" wrote in message
...
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

selecting month column
 

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

Thank you very much, sir.

DPingger

"Don Guillett" wrote:

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

"DPingger" wrote in message
...
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

"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

"DPingger" wrote:

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

"Patrick Molloy" wrote:

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" wrote in message
...
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

selecting month column
 

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DPingger" wrote in message
...
That is remarkably beautiful and elegant; eliminated a lot of my clutter.

Thank you very much, sir.

DPingger

"Don Guillett" wrote:

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

"DPingger" wrote in message
...
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

"Patrick Molloy" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
I fixed the formula error, but it still gives me a #N/A.

Any suggestions?

"DPingger" wrote:

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

"Patrick Molloy" wrote:

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" wrote in
message
...
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