Solved

Function needs to look at sheets in "created" workbook instead of original workbook

Posted on 2013-01-15
14
227 Views
Last Modified: 2013-01-18
Hi,

I am trying to combine two macros to eliminate a step. The current process is a two step process.

1. open workbook that has a macro, run it (it creates a new workbook).

2. Copy a macro from a text file into the newly created workbook and run it. (it creates additional sheets in the 2nd workbook and formats them.

I have successfully combined them but it missed the formatting of the created sheets. The problem is that the 2nd Macro has a function. When I put that Function at the top of the combined macro, it skips the steps because it doesn't see the sheets in the 2nd open workbook that was created by the Macro.

All the function is doing is looking for a sheet named "A" and if it finds it, proceed with formatting that sheet and if not, skip it and look for the next one. I think the the problem is that it's looking for the sheets in the first workbook instead of the workbook created by the Macro.  The 2nd workbook is always named "Book1.xlsx" with a sheet named "Car", then the macro will create a sheet named "A" so the function is looking for a sheet named "A" and I think it's looking in the first workbook instead of the newly created workbook.

First, here's the function:

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean
If xBook = "" Then xBook = ThisWorkbook.Name
Sheet_Exists = False
On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next
End Function

Second, here's how it's called in the Macro:

If Sheet_Exists("A") Then
    xFound = True
    xHold = xHold & "A" & Chr(10)

Hope that makes sense.

Thanks in advance.

swjtx99
0
Comment
Question by:swjtx99
  • 7
  • 7
14 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, swjtx99.

I'm not sure exactly what you want but the attached demonstrates access to a sheet in a newly created file.

Edit: The result should be that when you open the attached, you'll immediately find yourself in a newly created workbook with "Hello World!A" in A1.

The code is...
Option Explicit

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean
If xBook = "" Then xBook = ThisWorkbook.Name
Sheet_Exists = False
On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next
End Function

Sub Auto_Open()
Dim xBook As Workbook
Dim xFound As String
Dim xHold As String

Set xBook = Workbooks.Add

xHold = "Hello World!"

If Sheet_Exists("Sheet1", xBook.Name) Then
    xFound = True
    xHold = xHold & "A" & Chr(10)
End If

If xFound Then xBook.Sheets("Sheet1").Range("A1") = xHold

End Sub

Open in new window

Regards,
Brian.Test-Exists.xlsm
0
 

Author Comment

by:swjtx99
Comment Utility
Oops, I realized I forgot a part after reading your post:


Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean
If xBook = "" Then xBook = ThisWorkbook.Name
Sheet_Exists = False
On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next
End Function

Second, here's how it's called in the Macro: (I'm adding some of the formatting code to make this clearer.....hopefully)

Dim xCount As Long
Dim xFound As Boolean
Dim xHold  As String

If Sheet_Exists("A") Then
    xFound = True
    xHold = xHold & "A" & Chr(10)

    Sheets("A").Select
    Range([A1], [A65536].End(xlUp)).EntireRow.RowHeight = 16
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

If Sheet_Exists("B") Then
    xFound = True
    xHold = xHold & "B" & Chr(10)

    Sheets("B").Select
    Range([A1], [A65536].End(xlUp)).EntireRow.RowHeight = 16
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

This worked fine except now the function is in the macro that is in the original workbook and Sheet "A" and Sheet "B" are in the newly created workbook called "Book1.xlsx"

When I F8 through the macro, I can see it's skipping the formatting because (I think) the function is looking in the original workbook instead of "Book1.xlsx" because the sheets named A and B are clearly there, I can see them.

Sorry if I wasn't clear in the first post and thanks for taking a look.

swjtx99
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
swjtx99,

Thanks, but giving us partial code means that we have to spend time guessing what's going - time much better spent solving your problem!

Please either look at my first answer (it may well sort out your issues) or give us the full code (ideally with redacted files).

Thanks,
Brian.
0
 

Author Comment

by:swjtx99
Comment Utility
Hi Brian,

I see your point and I thought of that before posting the question but cleaning this up enough to be able to post would take hours so I was hoping there was a simple answer to pointing a function to another workbook.

In this part of the code (for example) it looks like it is making the current workbook active?

If xBook = "" Then xBook = ThisWorkbook.Name

What if it was changed to:

If xBook = "" Then xBook = ("Book1.xlsx")


Or would another possibility be to add:

set xBook = ("Book1.xlsx")

The Function is checking to see if a sheet of a specific name exists in a workbook. If yes, it formats the sheet, if no, it goes to the next section to check for the next sheet. The problem is I need it to check for the sheets in another workbook.

I'll see what I can do about posting an example file but I'm not hopeful :-)

Thanks again,

swjtx99
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
swjtx99,

I suggested two options - the first one does answer your question!

The Sheet_Exists macro has two parameters. The second one, xBook, is optional, but if present it's the name of the workbook that the macro uses. So you don't need your code change - just call Sheet_Exists as shown in my file above...
If Sheet_Exists("Sheet1", xBook.Name) Then

Regards,
Brian.
0
 

Author Comment

by:swjtx99
Comment Utility
Hi Brian,

Sorry for the delay but I'm still stuck. Your code creates a new WB but I've already created a new WB. You code seems to work on the WB it created but I can't figure out how to point it to mine. Please see below for where I create the sheet.

Option Explicit

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean
If xBook = "" Then xBook = ThisWorkbook.Name
Sheet_Exists = False
On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next
End Function
____________________________________________

Code for initial formatting, etc

Sheets("Car").Select
Sheets("Car").Copy 'copies sheet into a new WB

Code to create (add) sheets A, B, C, etc. in the newly created WB and move data from sheet "Car" onto them.

Then, I need the function to work on the WB already created:

Dim xCount As Long
Dim xFound As Boolean
Dim xHold  As String
Dim xBook As Workbook

Set xBook = Workbooks.Add ' this create another WB that I don't need.


If Sheet_Exists("A", xBook.Name) Then
    xFound = True
    xHold = xHold & "A" & Chr(10)

        Sheets("A").Select
    Range([A1], [A65536].End(xlUp)).EntireRow.RowHeight = 16
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End If

Sorry if I'm just not getting it and thanks for your help.

swjtx99
0
 

Author Comment

by:swjtx99
Comment Utility
Hi Brian,

I found an easy way to chop this down to an example I can post.

One note: Some of the part I chopped had a conflict with "Option Explicit" so is there a way to do this without using that?

Thanks,

swjtx99
Test-Example.xls
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, swjtx99.

Easy one first - you obviously worked out for yourself that you could simply delete the "Option Explicit". (Fine for a test, but never, under any circumstances, in live code!)

The macro runs, creates books and sheets, copies data in and formats things. No error messages. What problem should I be seeing?

BTW, the second line of the Cars() macro is "Sheets("Car").Copy" but it never does anything with this copy. (This could just be an effect of the stripping down.)

Thanks,
Brian.
0
 

Author Comment

by:swjtx99
Comment Utility
Hi Brian,

All the code is supposed to execute on the book created with that copy statement. First, it creates the new WB, then creates the A, B, C sheets but the formatting that is supposed to be done in the headers of A, B, C if they exist isn't getting done because the function is looking in the wrong book to check if A, B, or C exist (I think this is what is going on).

Does that help?

Thanks for sticking with me!

swjtx99
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
swjtx99,

(1) The lines starting "If Sheet_Exists(" for sheets "B", "C" and "D" did not include the workbook name. "A" is correct.

(2) When I run the Cars macro, the headers for "A","B" and "C" are being updated. As an example, please point me to a line of code that is looking in the wrong place.

BTW, if xBook refers to the required workbook, then its sheet "A" can be referenced by...
xBook.Sheets("A")
...does that help?

Regards,
Brian.
0
 

Author Comment

by:swjtx99
Comment Utility
Hi Brian,

Yes, I only did A for testing. Once I get that to work, I'll update B and C.

When I run the macro, it ends up at the print dialog box. I cancel out of that and there are 3 workbooks open.

1. The original WB that contains the macro
2. The WB I created in the Macro that has the sheet from the orignal workbook as well as A, B and C sheets.
3. the WB your code created that is blank that I do not need.

If you look at WB #2. on the Car sheet, there is header info in page layout view. Similar header info should be on sheet A (once I learn how to get on A, I'll update for B and C). If you go to the A sheet and change to page layout view, the header sections are blank.

The part of the code that starts with If Sheet_exists ("A") doesn't find "A".

I need it to execute the code below on sheet A of the WB I created (with the copy statement).....if sheet A exists (if not, go to B, C, etc.)

       Sheets("A").Select
    Range([A1], [A65536].End(xlUp)).EntireRow.RowHeight = 16
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

      Columns("A:I").Select
    Range("I1").Activate
    Columns("A:I").EntireColumn.AutoFit
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
   
'This section puts the info into the header for Cell A. .@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
     
 ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "&""Arial,Bold""&10WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Past Due = " & Application.WorksheetFunction.CountIf(Range("E1:E500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10% Past Due = " & Round(((Application.WorksheetFunction.CountIf(Range("E1:E500"), "<" & (Date * 1))) / (Cells.SpecialCells(xlCellTypeLastCell).Row - 1) * 100), 2) & "&""Arial,Bold"" % "
        .CenterHeader = "&""Arial,Bold""&14A WIP" & Chr(10) & "&""Arial,Bold""&10Oldest is " & (Date - [E2] + 4) & "&""Arial,Bold""&10 days "
        .RightHeader = Format(Date, "mm/dd/yyyy") & Chr(10) & Chr(10) & "&""Arial,Bold""&10Lab Past Due = " & Round(((Application.WorksheetFunction.CountIf(Worksheets("Car").Range("E1:E500"), "<" & (Date * 1))) / (Worksheets("Car").Cells.SpecialCells(xlCellTypeLastCell).Row - 1) * 100), 2) & "&""Arial,Bold"" % "
        .LeftFooter = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1.1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 20
        .PrintErrors = xlPrintErrorsDisplayed
    End With
   
        Columns("B:I").Select
    Range("I1").Activate
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   
    Application.CutCopyMode = False

Dim datum As Range
Dim firstfound As Range
Dim whichBorder As Variant

    lastrow = Range("A65536").End(xlUp).Row
    Set rng = Range("A1:I" & lastrow)

    With rng
        For Each whichBorder In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlDiagonalDown, xlDiagonalUp, xlInsideVertical, xlInsideHorizontal)
            .Borders(whichBorder).LineStyle = xlNone
        Next
        For Each whichBorder In Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
            .Borders(whichBorder).LineStyle = xlSingle
            .Borders(whichBorder).LineStyle = xlContinuous
        Next
    End With
End If
    Application.CutCopyMode = False
    Range("A2").Select
'___________________________________________________________________________________________________________

If Sheet_Exists("B") Then
    xFound = True
    xHold = xHold & "B" & Chr(10)

    Sheets("B").Select
    Range([A1], [A65536].End(xlUp)).EntireRow.RowHeight = 16
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

      Columns("A:I").Select
    Range("I1").Activate
    Columns("A:I").EntireColumn.AutoFit
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With

Thanks,

swjtx99
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
swjtx99,

the WB your code created that is blank that I do not need.
Then why did you add it to your file?! I've dropped all references to xBook in the following two options...

Option A - Set a variable for the new book.
(i) Add two lines at the start of Cars...
Dim xExtracted_Book As Workbook
Sheets("Car").Select
Sheets("Car").Copy

Set xExtracted_Book = ActiveWorkbook
(ii) Update the Sheet_Exists line as follows...
If Sheet_Exists("A", xExtracted_Book.Name) Then

Option B - Minimal
(i) Update the Sheet_Exists line as follows...
If Sheet_Exists("A", ActiveWorkbook.Name) Then

Don't forget that both options assume that the xBook lines have been dropped.

Brian.
0
 

Author Closing Comment

by:swjtx99
Comment Utility
Hi Brian,

Then why did you add it to your file?!
Because I don't know what I'm doing.

I went with Option B and everything works as hoped.

Thanks for your help. Sorry if I made this unecessarily painful by not posting the example file initially.

Regards,

swjtx99
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, swjtx99.

Sorry if I made this unnecessarily painful by not posting the example file initially.
Apology absolutely not necessary! From early on, I knew we were desperately close to a solution, but it took me a while to see exactly what you needed!

Regards,
Brian.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now