Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy, Paste and Rename a Range using a macro | Excel Discussion (Misc queries) | |||
Macro - copy a range and paste it in a new sheet 12 times | Excel Worksheet Functions | |||
Macro to copy, paste in a range and then delete | Excel Discussion (Misc queries) | |||
Copy/Paste using a macro that identifies occuoied range | Setting up and Configuration of Excel | |||
Macro to copy range of cells and paste into 1 sheet | Excel Programming |