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

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

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

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

Copy sheets only if condition is satisfied (mat)
 

no it's indeed OK

thanks a lot mate

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

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

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