Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save to a certain location | Excel Discussion (Misc queries) | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
SAVE IN LOCATION | Excel Worksheet Functions | |||
Cope cells | Excel Discussion (Misc queries) | |||
Save File to Another Directory, but not change Users File Save location | Excel Programming |