Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a spreadsheet depeding on a variable
Greetings, I would like to thank people in this forum for all the help I hav received. I would also like to ask for more of that kind help. I hav a list on a sheet named “Summary”. This list is for every day of th month. On the column next to it I have a formula that brings up th work “Conflict” next to the day depending on other criteria so thi word only appears next to the days that meet the criteria. I also hav another sheet named “Hourly View” with a template to format data in certain manner. I would like to create the “Hourly View” sheet for eac of the days in the month that have the work “Conflict” next to them. I addition, if possible, I would like to rename each of the new tabs wit the day of month that it was created for. Thank you so much for an help -- oakma ----------------------------------------------------------------------- oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917 View this thread: http://www.excelforum.com/showthread.php?threadid=56701 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a spreadsheet depeding on a variable
without knowing the layout of your data, my thought would be to use a pivot
table to get different views. It is found under the data menu. Debra Dalgleish has plenty of information at http://www.contextures.com under tech tips. "oakman" wrote: Greetings, I would like to thank people in this forum for all the help I have received. I would also like to ask for more of that kind help. I have a list on a sheet named €śSummary€ť. This list is for every day of the month. On the column next to it I have a formula that brings up the work €śConflict€ť next to the day depending on other criteria so this word only appears next to the days that meet the criteria. I also have another sheet named €śHourly View€ť with a template to format data in a certain manner. I would like to create the €śHourly View€ť sheet for each of the days in the month that have the work €śConflict€ť next to them. In addition, if possible, I would like to rename each of the new tabs with the day of month that it was created for. Thank you so much for any help! -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=567011 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a spreadsheet depeding on a variable
Hello Tom, Thank you for the response. Pivot Tables are a good suggestion, but that is exactly what the previous existance of this report was based on. I am actually trying to create an alternative to Pivot Tables in the new format. While Pivoting is efficient, the raw data that I have does not lend it self to making Pivoting an efficient process. I have tried to come up with some code, so far I can get to the point where a sheet is added triggered by the word "Conflict" in my "SUMMARY" sheet. However, I get an error when I tell it to copy the desired range. Can you take a look at my code and make some suggestions please? Your help is very much appreciated. Sub addsheet() Dim Day As Range, rng As Range Dim sh As Worksheet Sheets("SUMMARY").Select Range("B3").Select For Each Day In Sheets("SUMMARY").Range("B3:B33") Set sh = Worksheets(Day.Value) Set rng = Sheets("Hourly View").Range("A1:Z48") If Day.Offset(0, 1) = "Conflict" Then Worksheets.Add.Copy rng End If Next Day End Sub -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=567011 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a spreadsheet depeding on a variable
Sub addsheet()
Dim Day As Range, rng As Range Dim sh As Worksheet Sheets("SUMMARY").Select Range("B3").Select For Each Day In Sheets("SUMMARY").Range("B3:B33") Set sh = Worksheets(Day.Value) Set rng = Sheets("Hourly View").Range("A1:Z48") If Day.Offset(0, 1) = "Conflict" Then Worksheets.Add After:=Worksheets(worksheets.count) rng.copy Destination:=Activesheet.Range("A1') Activesheet.name = Day.value End If Next Day End Sub would be me guess. -- Regards, Tom Ogilvy "oakman" wrote: Hello Tom, Thank you for the response. Pivot Tables are a good suggestion, but that is exactly what the previous existance of this report was based on. I am actually trying to create an alternative to Pivot Tables in the new format. While Pivoting is efficient, the raw data that I have does not lend it self to making Pivoting an efficient process. I have tried to come up with some code, so far I can get to the point where a sheet is added triggered by the word "Conflict" in my "SUMMARY" sheet. However, I get an error when I tell it to copy the desired range. Can you take a look at my code and make some suggestions please? Your help is very much appreciated. Sub addsheet() Dim Day As Range, rng As Range Dim sh As Worksheet Sheets("SUMMARY").Select Range("B3").Select For Each Day In Sheets("SUMMARY").Range("B3:B33") Set sh = Worksheets(Day.Value) Set rng = Sheets("Hourly View").Range("A1:Z48") If Day.Offset(0, 1) = "Conflict" Then Worksheets.Add.Copy rng End If Next Day End Sub -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=567011 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a spreadsheet depeding on a variable
Hello Tom, I would like to thank you for the suggestions that you have provided. The macro works very well. This adds a great amount of efficiency to my daily tasks. Again, thank you! -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=567011 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable data from one spreadsheet sent to new spreadsheet | New Users to Excel | |||
Macro to copy and append spreadsheet changes to new spreadsheet | Excel Discussion (Misc queries) | |||
How do I copy spreadsheet data as shown to another spreadsheet? | Excel Discussion (Misc queries) | |||
Searching array for date and changing offset colour depeding on combobox content? | Excel Programming | |||
How to open another Excel spreadsheet to copy data into current spreadsheet ? | Excel Programming |