copy formula

viper

Active Member
Joined
Feb 15, 2002
Messages
382
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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.

ni****h desai
http://www.pexcel.com
This message was edited by nisht on 2002-03-31 00:18
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top