Copy sheets only if condition is satisfied (mat)

[matthias]

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

 

[Ron de Bruin]

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

 

[matthias]

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

 

[Ron de Bruin]

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

 

[matthias]

no it's indeed OK

thanks a lot mate

 

[matthias]

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

 

[Ron de Bruin]

you can delete the shapes on the activesheet with this


Sub Forms1()'Delete All Forms buttons ActiveSheet.Buttons.DeleteEnd Sub

 

[Ron de Bruin]

This is better

Sub Forms1()
'Delete All Forms buttons
ActiveSheet.Buttons.Delete
End Sub