Copy sheet to different workbook if ......

If any one of cells B31 to B46 = Booked I need to copy the sheet named 'Booked' from the workbook I am in to another workbook.

When my Macro creates the second workbook I use:

Dim k As Workbook
            Set k = ActiveWorkbook

So I would need to copy the sheet from mt Activeworkbook to k [workbook]

Would appreciate an Experts help

Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
basically something like this

Option Explicit

Sub CopyShr()
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31@B46", "Booked")) > 0 Then
ActiveSheet.Copy Workbooks("wahever tha other workbook is called")
End If
End Sub

Open in new window


Provide  the name of the destination workbook and I'll edit the code.
0
JagwarmanAuthor Commented:
The name of the destination workbook changes each time the macro runs.

When my Macro creates the second workbook I save it using data from cell B51 in the active workbook which is why it changes every time the macro runs. When the new workbook has been saved I then say

 Dim k As Workbook
             Set k = ActiveWorkbook

so k is the new workbook to flip between the original and the new.
0
Roy CoxGroup Finance ManagerCommented:
The code that I posted so far will check the range for any Booked entries. So to save as a value in B51

Option Explicit

Sub CopyShr()
Dim K As Workbook
'check for "Booked"
If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31@B46", "Booked")) > 0 Then
'if any entries exist copy the active sheet
ActiveSheet.Copy
ActiveWorkbook.SaveAs "C:\" ^ Range("B51").Value, FileFormat:=51 '/xlzsx. change to 52 for xlsm
Set K = ActiveWorkbook
End If
End Sub

Open in new window


Post back for further help if required
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Martin LissOlder than dirtCommented:
Roy's line 6 should be

If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31:B46"), "Booked") > 0 Then
0
Roy CoxGroup Finance ManagerCommented:
Thanks for spotting the typo Martin
0
RonBudget AnalystCommented:
I would Dim object variables for both the Original workbook (WBO) and New workbook (WBN) and string variables for the destinaltion path and filename. So,  
Dim WBO as Workbook, WBN as Workbook
Dim WSO as Worksheet, WSN as Worksheet
Dim Path as String, sFilename as String, sFullPath as String

Set WBO = ThisWorkbook
Set WSO = Sheets ("your sheet name" )
sPath = ("Your destination folder")
sFilename = WSO.Range ("Your range with the filename").value
sFullPath = sPath & "/" & sFilename & ".xlsx"

then, when you copy the activesheet, use this syntax
WSO.Copy sFullPath
0
JagwarmanAuthor Commented:
Roy I get Compile error Argument not optional on Countif

If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31:B46", "Repo")) > 0 Then
0
Roy CoxGroup Finance ManagerCommented:
Sorry, try amended code

Option Explicit

Sub CopyShr()
    Dim K As Workbook
    'check for "Booked"
    If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31:B46"), "Booked") > 0 Then
        'if any entries exist copy the active sheet
        ActiveSheet.Copy
        ActiveWorkbook.SaveAs "C:\" ^ Range("B51").Value, _
                              FileFormat:=51    '/xlzsx. change to 52 for xlsm
        Set K = ActiveWorkbook
    End If
End Sub

Open in new window

0
JagwarmanAuthor Commented:
Hi Roy, sorry to be a nuisance but as I mentioned in my original question, I have already created the new file and it is the new file I need to copy the sheet 'Booked' into. The file I have already created contains other sheets and the sheet 'Booked' will be the third sheet in the file to be added.

so when I copy the sheet 'Booked' I need to say something like

K.Activate
Worksheets.Add(After:=Worksheets("Ace")).Name = "Booked"

then paste the 'Booked' details into that sheet.

sorry for confusion.

Regards
0
Roy CoxGroup Finance ManagerCommented:
Will the destination workbook be open. If so add the name into this code

Option Explicit


Sub CopyShr()
    Dim TargetWb  As Workbook
    'check for "Booked"
    If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31:B46"), "Booked") > 0 Then
        'if any entries exist copy the active sheet
        ActiveSheet.Copy After:=Workbooks("OtherWorkBookNameHere").Worksheets.Count
        ActiveWorkbook.Save
    End If
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
I'm sorry but think I am confusing you. I never know what the name of the new workbook is which is why when it is created I use

Dim k As Workbook
             Set k = ActiveWorkbook

and then refer to it as k

so I cannot enter the name of the workbook in the code.
0
JagwarmanAuthor Commented:
Hi Roy,

So using part of your code I then did this

If Application.WorksheetFunction.CountIf(ActiveSheet.Range("B31:B46"), "Booked") > 0 Then
       
        Sheets("Booked").Columns("B:B").Copy
       
        K.Activate
        Worksheets.Add(After:=Worksheets("Ace")).Name = "Booked"

        ActiveSheet.Paste

And it does exactly what I need.

So thanks for your help and making me thing about what I needed to do. Clearly your code will help other people so long as they know the name of the file.
0
JagwarmanAuthor Commented:
Almost what I was looking for but definitely helped me greatly get to the solution.
0
Roy CoxGroup Finance ManagerCommented:
Glad it helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.