Cope worksheets and save to location
 

Hi All

Ok, what I have is a workbook with a few sheets. Sheet1 is the data
sheet with rows of data. Col A has the group information and column B
has the data associated with it. For example, there may be 10 rows for
group 1, 5 rows for group 2 etc etc, the number of rows per group may
change depending on how much data they have.

Sheet 2 and sheet 3 are the report set up. In cell c1 of sheet 2, I
have the name of the group and rest of the report in sheets 2 and 3
pulls up the data relating to that group from sheet1, but that part is
ok.

What I am trying to do is set up a macro to copy sheet 2 and sheet 3 to
a new file, delete the formulas in it to only have values, save it to a
location and then go back to the main file to change what's in cell c1
in sheet2 to refelect whichever group is next in col A in sheet1, which
will automatically populate rest of the pages with that group
information and then copy the new sheets 2 and 3 and save it the same
location as something else and go back to main file, loop until it gets
to the last group in col A of sheet 1 and stop.

I've been tearting my hair out for 3 hours and no joy :( please
somebody help!!


Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=381941

Cope worksheets and save to location
 

A start would be some of the canned copy routines at Ron de Bruin's site:

http://www.rondebruin.nl/tips.htm


set sh = thisworkBookWorksheets(1)
' copy worksheets to new workbook
worksheets(Array("Sheet2","Sheet3")).Copy
for i = 1 to 2
set rng = Activeworkbook.Worksheets(i).Cells
rng.Formula = rng.Value
Next
ActiveWorkbook.SaveAs "C:\MyFiles\" & sh.Range("C1").Value & ".xls"
ActiveWorkbook.Close SaveChanges:=False



--
Regards,
Tom Ogilvy


"ozcank" wrote in
message ...

Hi All

Ok, what I have is a workbook with a few sheets. Sheet1 is the data
sheet with rows of data. Col A has the group information and column B
has the data associated with it. For example, there may be 10 rows for
group 1, 5 rows for group 2 etc etc, the number of rows per group may
change depending on how much data they have.

Sheet 2 and sheet 3 are the report set up. In cell c1 of sheet 2, I
have the name of the group and rest of the report in sheets 2 and 3
pulls up the data relating to that group from sheet1, but that part is
ok.

What I am trying to do is set up a macro to copy sheet 2 and sheet 3 to
a new file, delete the formulas in it to only have values, save it to a
location and then go back to the main file to change what's in cell c1
in sheet2 to refelect whichever group is next in col A in sheet1, which
will automatically populate rest of the pages with that group
information and then copy the new sheets 2 and 3 and save it the same
location as something else and go back to main file, loop until it gets
to the last group in col A of sheet 1 and stop.

I've been tearting my hair out for 3 hours and no joy :( please
somebody help!!


Oz


--
ozcank


------------------------------------------------------------------------
ozcank's Profile:
http://www.excelforum.com/member.php...fo&userid=5328
View this thread: http://www.excelforum.com/showthread...hreadid=381941

Cope worksheets and save to location
 

Thanks Tom, I'll give this a g

--
ozcan

-----------------------------------------------------------------------
ozcank's Profile: http://www.excelforum.com/member.php...nfo&userid=532
View this thread: http://www.excelforum.com/showthread.php?threadid=38194