copy formula

[viper]

My workbook was finished or so I thought. Anyway, I'm wanting to add some code to do some sheet copying. I have 11 sheets, two sheets a week, plus the main sheet. What I'm wanting to do is copy these sheets to another workbook based on a cell value. I just tried to do the following:

Sheet1.select (this sheet will always be copied)
Sheet2.Activate
If Range("B9") <> " " then
Sheet2.Select
Sheet7.select
end if
Sheet3.Activate
If Range("B9") <> " " Then
Sheet3.Select
Sheet8.Select
end if
Sheet4.Activate
If Range("B9") <> " " Then
Sheet4.Select
Sheet9.Select
end if
Sheet5.Activate
If Range("B9") <> " " Then
Sheet5.Select
Sheet10.Select
End if
Selection.Copy
ActiveWorkbook.SaveAs "FileName"

I get an error with the above code. What am I doing wrong.

[nisht]

PLEASE NOTE:

TAKE BACKUP OF YOUR ORIGINAL WORKBOOK BEFORE
YOU TRY THIS.
I HAVE NOT check the code so if it solves your problem..atleast inform me.

Sub modifiedworkbookbackup()
Dim sht As Worksheet
For Each sht In Workbooks("allexperts").Worksheets
If sht.[b9] <> "" Then
'
sht.Delete
End If
Next sht
Application.GetSaveAsFilename "Your file Name"
' You can save your file in number of formats.
'ThisWorkbook.SaveAs "d:myback" & Worksheets("sheet4").Range("a1").Text, xlNormal
End Sub


Please study below code also

Sub workbooksave()
' to make a save in current working folder
' use below code
'ThisWorkbook.SaveAs Worksheets("sheet4").Range("a1").Text
' -------------------------------
' If you want to saveas in different folder then give the
' complete path and dont forget to put back slash
' at the end of the folder where you want to change
' the folder otherwise file will be save with the
' last word and the filename suggested
ThisWorkbook.SaveAs "d:myback" & Worksheets("sheet4").Range("a1").Text
End Sub


Sub workbookopennetwork()

Application.ScreenUpdating = False

' if we want to open work book in network machine and directory in the network then
Workbooks.Open "\AbhishekcMy DocumentsVadnagar.xls"

Application.ScreenUpdating = True


End Sub

I hope this will help you.

nishith desai
http://www.pexcel.com


[ This Message was edited by: nisht on 2002-03-31 00:18 ]

[Dave Hawley]

Hi Viper

Following along the same lines a nisht's code, try approaching these type of problems from the other angle. Instead of copying all sheets which have a value in B9, copy all sheets then delete the ones not needed.


Sub DoIt()
Dim wsSheet As Worksheet

Sheets.Copy
Application.DisplayAlerts = False

For Each wsSheet In ActiveWorkbook.Worksheets
If wsSheet.Range("B9") = "" Then wsSheet.Delete
Next wsSheet

Application.DisplayAlerts = True
ActiveWorkbook.SaveAs "FileName"
End Sub