macro to copy/paste range for printing
 

I have a spreadsheet with weekly sales in which the first column has the
list of categories such as 'Residential', 'Commercial', Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the year.

I am looking for a way to allow the user to easily print this for any
particular month without having to manually select the columns for that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the month to
print or 'From Col' and 'To Col' that would copy and paste the data where it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am familiar
with other programming languages. Any help from the Excel/VB gurus would be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

Since this is set up like a database table, it might be easier to select the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can select
the dropdown for the date column and select custom. then specify the range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again. (or to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You would need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column has the
list of categories such as 'Residential', 'Commercial', Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the year.

I am looking for a way to allow the user to easily print this for any
particular month without having to manually select the columns for that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the month to
print or 'From Col' and 'To Col' that would copy and paste the data where it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am familiar
with other programming languages. Any help from the Excel/VB gurus would be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be because of the
way the sheet is formatted. It is like this:

01/06/06 01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD and WKLY.
Hopefully this message will post looking something like the spreadsheet.

I was also playing around with having the user enter the start column and
end column of the data to be printed and using those to calculate the range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they enter the
start column and G2 is the end column. This gives me B5:I40 which I was
hoping to use in a macro to copy/paste the range. Am I on the right track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to select
the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can select
the dropdown for the date column and select custom. then specify the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again. (or to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column has the
list of categories such as 'Residential', 'Commercial', Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the year.

I am looking for a way to allow the user to easily print this for any
particular month without having to manually select the columns for that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the month
to
print or 'From Col' and 'To Col' that would copy and paste the data where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am
familiar
with other programming languages. Any help from the Excel/VB gurus would
be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

set rng = Range(Range("E2".Value) &"5:" & _
Range("G2").Value &"40")

rng.printout

should work.

--
Regards,
Tom Ogilvy




"Mike Wasilewski" wrote:

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be because of the
way the sheet is formatted. It is like this:

01/06/06 01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD and WKLY.
Hopefully this message will post looking something like the spreadsheet.

I was also playing around with having the user enter the start column and
end column of the data to be printed and using those to calculate the range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they enter the
start column and G2 is the end column. This gives me B5:I40 which I was
hoping to use in a macro to copy/paste the range. Am I on the right track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to select
the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can select
the dropdown for the date column and select custom. then specify the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again. (or to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column has the
list of categories such as 'Residential', 'Commercial', Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the year.

I am looking for a way to allow the user to easily print this for any
particular month without having to manually select the columns for that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the month
to
print or 'From Col' and 'To Col' that would copy and paste the data where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am
familiar
with other programming languages. Any help from the Excel/VB gurus would
be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

Tom,

Thanks again for the reply! I think I am on the right track. Here's the
code I have

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues

When run, it causes the the worksheet to "flash" continually until I click
on the "PRINT" worksheet tab. The data shows up on the PRINT worksheet but
it does not have any of the formatting (borders, col widths, etc) because of
the PastSpecial command that I am using. How can I get the copy to retain
everything but bring values instead of the formulas?

Thanks again,

Mike

"Tom Ogilvy" wrote in message
...
set rng = Range(Range("E2".Value) &"5:" & _
Range("G2").Value &"40")

rng.printout

should work.

--
Regards,
Tom Ogilvy




"Mike Wasilewski" wrote:

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter
options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be because of
the
way the sheet is formatted. It is like this:

01/06/06 01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD and
WKLY.
Hopefully this message will post looking something like the spreadsheet.

I was also playing around with having the user enter the start column and
end column of the data to be printed and using those to calculate the
range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they enter
the
start column and G2 is the end column. This gives me B5:I40 which I was
hoping to use in a macro to copy/paste the range. Am I on the right
track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to
select
the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can
select
the dropdown for the date column and select custom. then specify the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again. (or
to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You
would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column has
the
list of categories such as 'Residential', 'Commercial',
Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the year.

I am looking for a way to allow the user to easily print this for any
particular month without having to manually select the columns for
that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the
month
to
print or 'From Col' and 'To Col' that would copy and paste the data
where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am
familiar
with other programming languages. Any help from the Excel/VB gurus
would
be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

PasteSpecial xlPasteValues

only pastes the values. If you want to paste the formats as well add a line

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues
target.PasteSpecial xlPasteFormats
target.PasteSpecial xlPasteColumnWidths

There is no command to paste row heights, but you didn't mention that as a
problem.

--
Regards,
Tom Ogilvy






"Mike Wasilewski" wrote:



Tom,

Thanks again for the reply! I think I am on the right track. Here's the
code I have

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues

When run, it causes the the worksheet to "flash" continually until I click
on the "PRINT" worksheet tab. The data shows up on the PRINT worksheet but
it does not have any of the formatting (borders, col widths, etc) because of
the PastSpecial command that I am using. How can I get the copy to retain
everything but bring values instead of the formulas?

Thanks again,

Mike

"Tom Ogilvy" wrote in message
...
set rng = Range(Range("E2".Value) &"5:" & _
Range("G2").Value &"40")

rng.printout

should work.

--
Regards,
Tom Ogilvy




"Mike Wasilewski" wrote:

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter
options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be because of
the
way the sheet is formatted. It is like this:

01/06/06 01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD and
WKLY.
Hopefully this message will post looking something like the spreadsheet.

I was also playing around with having the user enter the start column and
end column of the data to be printed and using those to calculate the
range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they enter
the
start column and G2 is the end column. This gives me B5:I40 which I was
hoping to use in a macro to copy/paste the range. Am I on the right
track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to
select
the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can
select
the dropdown for the date column and select custom. then specify the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again. (or
to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You
would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column has
the
list of categories such as 'Residential', 'Commercial',
Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the year.

I am looking for a way to allow the user to easily print this for any
particular month without having to manually select the columns for
that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the
month
to
print or 'From Col' and 'To Col' that would copy and paste the data
where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am
familiar
with other programming languages. Any help from the Excel/VB gurus
would
be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

Tom,

Thanks alot! That does the trick. Now, I have this code associated with
the template that I am creating. How can I make it available and functional
for other sheets in the workbook? I have a seperate sheet for 2004, 2005,
2006 and TEMPLATE. I was trying to use ActiveSheet in the range commands
but could not gt it to work.

Mike


"Tom Ogilvy" wrote in message
...
PasteSpecial xlPasteValues

only pastes the values. If you want to paste the formats as well add a
line

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues
target.PasteSpecial xlPasteFormats
target.PasteSpecial xlPasteColumnWidths

There is no command to paste row heights, but you didn't mention that as a
problem.

--
Regards,
Tom Ogilvy






"Mike Wasilewski" wrote:



Tom,

Thanks again for the reply! I think I am on the right track. Here's the
code I have

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues

When run, it causes the the worksheet to "flash" continually until I
click
on the "PRINT" worksheet tab. The data shows up on the PRINT worksheet
but
it does not have any of the formatting (borders, col widths, etc) because
of
the PastSpecial command that I am using. How can I get the copy to
retain
everything but bring values instead of the formulas?

Thanks again,

Mike

"Tom Ogilvy" wrote in message
...
set rng = Range(Range("E2".Value) &"5:" & _
Range("G2").Value &"40")

rng.printout

should work.

--
Regards,
Tom Ogilvy




"Mike Wasilewski" wrote:

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter
options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be because
of
the
way the sheet is formatted. It is like this:

01/06/06
01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD and
WKLY.
Hopefully this message will post looking something like the
spreadsheet.

I was also playing around with having the user enter the start column
and
end column of the data to be printed and using those to calculate the
range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they
enter
the
start column and G2 is the end column. This gives me B5:I40 which I
was
hoping to use in a macro to copy/paste the range. Am I on the right
track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to
select
the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can
select
the dropdown for the date column and select custom. then specify
the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again.
(or
to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You
would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column
has
the
list of categories such as 'Residential', 'Commercial',
Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the
year.

I am looking for a way to allow the user to easily print this for
any
particular month without having to manually select the columns for
that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the
month
to
print or 'From Col' and 'To Col' that would copy and paste the data
where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am
familiar
with other programming languages. Any help from the Excel/VB gurus
would
be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

sounds like you are putting your code in the sheet module. Unless it is
associated with an events, such as a click event for a commandbutton or a
selectionchange for the worksheet (as examples), code is best placed in a
general module

insert=Module

in the VBE.

Even if it be triggered by a commandbutton click or other event, you can put
the general code in a procedure in the the general module, and call that
module from the event code.

Generally you can just take your code and copy it to a general module.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

Tom,

Thanks alot! That does the trick. Now, I have this code associated with
the template that I am creating. How can I make it available and functional
for other sheets in the workbook? I have a seperate sheet for 2004, 2005,
2006 and TEMPLATE. I was trying to use ActiveSheet in the range commands
but could not gt it to work.

Mike


"Tom Ogilvy" wrote in message
...
PasteSpecial xlPasteValues

only pastes the values. If you want to paste the formats as well add a
line

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues
target.PasteSpecial xlPasteFormats
target.PasteSpecial xlPasteColumnWidths

There is no command to paste row heights, but you didn't mention that as a
problem.

--
Regards,
Tom Ogilvy






"Mike Wasilewski" wrote:



Tom,

Thanks again for the reply! I think I am on the right track. Here's the
code I have

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues

When run, it causes the the worksheet to "flash" continually until I
click
on the "PRINT" worksheet tab. The data shows up on the PRINT worksheet
but
it does not have any of the formatting (borders, col widths, etc) because
of
the PastSpecial command that I am using. How can I get the copy to
retain
everything but bring values instead of the formulas?

Thanks again,

Mike

"Tom Ogilvy" wrote in message
...
set rng = Range(Range("E2".Value) &"5:" & _
Range("G2").Value &"40")

rng.printout

should work.

--
Regards,
Tom Ogilvy




"Mike Wasilewski" wrote:

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter
options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be because
of
the
way the sheet is formatted. It is like this:

01/06/06
01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD and
WKLY.
Hopefully this message will post looking something like the
spreadsheet.

I was also playing around with having the user enter the start column
and
end column of the data to be printed and using those to calculate the
range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they
enter
the
start column and G2 is the end column. This gives me B5:I40 which I
was
hoping to use in a macro to copy/paste the range. Am I on the right
track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to
select
the
data and do Date=filter=Autofilter in the menus. This will place
dropdowns in the top cell of the selection in each column. You can
select
the dropdown for the date column and select custom. then specify
the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again.
(or
to
leave the dropdowns and show all the data do Data=Filter=Show All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option. You
would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column
has
the
list of categories such as 'Residential', 'Commercial',
Reconstruction',
etc.

column B and the columns beyond have a date for the heading such as
'1/06/06', '1/13/06', ... corresponding to each Friday for the
year.

I am looking for a way to allow the user to easily print this for
any
particular month without having to manually select the columns for
that
month and pasting them into an area with a heading or onto another
worksheet.

I would like to create a macro that would take input as either the
month
to
print or 'From Col' and 'To Col' that would copy and paste the data
where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I am
familiar
with other programming languages. Any help from the Excel/VB gurus
would
be
greatly appreciated.

Mike

macro to copy/paste range for printing
 

Tom,

I moved the code and all is fine! Thanks alot for your help.

Mike

"Tom Ogilvy" wrote in message
...
sounds like you are putting your code in the sheet module. Unless it is
associated with an events, such as a click event for a commandbutton or a
selectionchange for the worksheet (as examples), code is best placed in a
general module

insert=Module

in the VBE.

Even if it be triggered by a commandbutton click or other event, you can
put
the general code in a procedure in the the general module, and call that
module from the event code.

Generally you can just take your code and copy it to a general module.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

Tom,

Thanks alot! That does the trick. Now, I have this code associated with
the template that I am creating. How can I make it available and
functional
for other sheets in the workbook? I have a seperate sheet for 2004,
2005,
2006 and TEMPLATE. I was trying to use ActiveSheet in the range commands
but could not gt it to work.

Mike


"Tom Ogilvy" wrote in message
...
PasteSpecial xlPasteValues

only pastes the values. If you want to paste the formats as well add a
line

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues
target.PasteSpecial xlPasteFormats
target.PasteSpecial xlPasteColumnWidths

There is no command to paste row heights, but you didn't mention that
as a
problem.

--
Regards,
Tom Ogilvy






"Mike Wasilewski" wrote:



Tom,

Thanks again for the reply! I think I am on the right track. Here's
the
code I have

Dim rng As Range
Dim target As Range
Set rng = Range(Range("E2").Value & "5:" & _
Range("G2").Value & "40")
Set target = Sheets("PRINT").Range("B3")
rng.Copy
target.PasteSpecial xlPasteValues

When run, it causes the the worksheet to "flash" continually until I
click
on the "PRINT" worksheet tab. The data shows up on the PRINT
worksheet
but
it does not have any of the formatting (borders, col widths, etc)
because
of
the PastSpecial command that I am using. How can I get the copy to
retain
everything but bring values instead of the formulas?

Thanks again,

Mike

"Tom Ogilvy" wrote in message
...
set rng = Range(Range("E2".Value) &"5:" & _
Range("G2").Value &"40")

rng.printout

should work.

--
Regards,
Tom Ogilvy




"Mike Wasilewski" wrote:

Tom,

Thanks for the speedy reply! I tried playing with the DataFilter
options
and I get drop down boxes with alot of different values in them
corresponding to all of the data in the column. This may be
because
of
the
way the sheet is formatted. It is like this:

01/06/06
01/13/06
YTD WKLY YTD WKLY

Actual Reconstruction 9990 1512 12520 1718
Actual Residential 1515 123 1695 687
Actual Commercial 1803 685 2106 543
....

The dates are in 2 merged cells that span the columns holding YTD
and
WKLY.
Hopefully this message will post looking something like the
spreadsheet.

I was also playing around with having the user enter the start
column
and
end column of the data to be printed and using those to calculate
the
range.

I used this formula: =$E$2&"5:"&$G$2&"40" where E2 is where they
enter
the
start column and G2 is the end column. This gives me B5:I40 which
I
was
hoping to use in a macro to copy/paste the range. Am I on the
right
track
or just spinning my wheels?

Thanks,

Mike

"Tom Ogilvy" wrote in message
...
Since this is set up like a database table, it might be easier to
select
the
data and do Date=filter=Autofilter in the menus. This will
place
dropdowns in the top cell of the selection in each column. You
can
select
the dropdown for the date column and select custom. then
specify
the
range
of dates. Then print the sheet.

To remove the filter dropdowns to Data=Filter=Autofilter again.
(or
to
leave the dropdowns and show all the data do Data=Filter=Show
All

If you actually want to copy the data, you can look at
Data=Filter=Advanced Filter and select the copy to option.
You
would
need
to set up a criteria range where the users put the criteria.

--
Regards,
Tom Ogilvy


"Mike Wasilewski" wrote:

I have a spreadsheet with weekly sales in which the first column
has
the
list of categories such as 'Residential', 'Commercial',
Reconstruction',
etc.

column B and the columns beyond have a date for the heading such
as
'1/06/06', '1/13/06', ... corresponding to each Friday for the
year.

I am looking for a way to allow the user to easily print this
for
any
particular month without having to manually select the columns
for
that
month and pasting them into an area with a heading or onto
another
worksheet.

I would like to create a macro that would take input as either
the
month
to
print or 'From Col' and 'To Col' that would copy and paste the
data
where
it
is needed so the user can print the report.

I am not familiar with VB and do not use Excel very often, but I
am
familiar
with other programming languages. Any help from the Excel/VB
gurus
would
be
greatly appreciated.

Mike