Copy a spreadsheet depeding on a variable

[oakman]

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!

[Tom Ogilvy]

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
>
>

[oakman]

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

[Tom Ogilvy]

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
>
>

[oakman]

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!