Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
Hi guys,
i want a macro to do the following: I have a masterfile with several sheets. If I run the macro i want it to copy sheet1 and/orsheet2 and /or sheet 3 to a new workbook depending on a condition. Sheet 1 has to be copied always Sheet 2 has to be copied only if the cell A2 from sheet 1 is the same as the cell A2 from sheet 2 Sheet 3 has to be copied only if the cell A2 from sheet 1 is the same as the cell A2 from sheet3 the sheets have to be copied as values and with the format off the sheets from the masterfile. Thanks guys mat |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
Hi matthias
Try this basic code example Sub TestCopy() If Sheets("Sheet1").Range("A2").Value = Sheets("Sheet2").Range("A2").Value Then If Sheets("Sheet1").Range("A2").Value = Sheets("Sheet3").Range("A2").Value Then Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy Else Sheets(Array("Sheet1", "Sheet2")).Copy End If Else If Sheets("Sheet1").Range("A2").Value = Sheets("Sheet3").Range("A2").Value Then Sheets(Array("Sheet1", "Sheet3")).Copy Else Sheets("Sheet1").Copy End If End If Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).Select Application.CutCopyMode = False End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "matthias" wrote in message oups.com... Hi guys, i want a macro to do the following: I have a masterfile with several sheets. If I run the macro i want it to copy sheet1 and/orsheet2 and /or sheet 3 to a new workbook depending on a condition. Sheet 1 has to be copied always Sheet 2 has to be copied only if the cell A2 from sheet 1 is the same as the cell A2 from sheet 2 Sheet 3 has to be copied only if the cell A2 from sheet 1 is the same as the cell A2 from sheet3 the sheets have to be copied as values and with the format off the sheets from the masterfile. Thanks guys mat |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
thanks ron!! now, can I ask you another thing?
now the macro runs perfectly, but can i also make sure that the copies of the sheets are in "landscape form" as the originals? thanks ps ik spreek ook nederlands |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
I test it with one of the sheets set to landscape and in the new workbook it is also landscape ?
Do you get a different result -- Regards Ron de Bruin http://www.rondebruin.nl "matthias" wrote in message ups.com... thanks ron!! now, can I ask you another thing? now the macro runs perfectly, but can i also make sure that the copies of the sheets are in "landscape form" as the originals? thanks ps ik spreek ook nederlands |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
no it's indeed OK
thanks a lot mate |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
hi maybe one extra question
if i run the macro via a button, he copies the button to the new workbook since it is part of sheet1. how can i avoid this. if know it is something with shapes...(it is a button from forms), it has to be put in the code above? mat |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
you can delete the shapes on the activesheet with this
Sub Forms1()'Delete All Forms buttons ActiveSheet.Buttons.DeleteEnd Sub -- Regards Ron de Bruin http://www.rondebruin.nl "matthias" wrote in message oups.com... hi maybe one extra question if i run the macro via a button, he copies the button to the new workbook since it is part of sheet1. how can i avoid this. if know it is something with shapes...(it is a button from forms), it has to be put in the code above? mat |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheets only if condition is satisfied (mat)
This is better
Sub Forms1() 'Delete All Forms buttons ActiveSheet.Buttons.Delete End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... you can delete the shapes on the activesheet with this Sub Forms1()'Delete All Forms buttons ActiveSheet.Buttons.DeleteEnd Sub -- Regards Ron de Bruin http://www.rondebruin.nl "matthias" wrote in message oups.com... hi maybe one extra question if i run the macro via a button, he copies the button to the new workbook since it is part of sheet1. how can i avoid this. if know it is something with shapes...(it is a button from forms), it has to be put in the code above? mat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to get data when condition is satisfied | Excel Worksheet Functions | |||
countif and sum if if two condition are satisfied | Excel Worksheet Functions | |||
Looping till condition is satisfied? | Excel Worksheet Functions | |||
How to change the value of a cell when a condition is satisfied, but not otherwise? | Excel Discussion (Misc queries) | |||
Deletion of rows where a value is satisfied for all the sheets | Excel Programming |