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

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

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

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

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