+ Reply to Thread
Results 1 to 6 of 6

Activesheet Method not working in my Macro

  1. #1
    Registered User
    Join Date
    08-30-2012
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    38

    Activesheet Method not working in my Macro

    Hi I have created a macro for my checklist, when the macro is opened it will hide all the sheets except for 'Home' sheet, I have wrote code for hiding sheets in 'ThisWorkbook' object. Home Sheet will have 3 buttons, when I click on the 1st button it will take us to a form, where in it have some controls like combo-box and list-box. upon particular selections made from these combo and list box, a sheet will be unhidden (there are several sheets hidden) which is protected by code. Post that, I will select some check points using data validation in column D, after that there is a drawing object (arrow key) by clicking on this it will take me back to the home sheet and enables 2nd button. when I click on this 2nd button, another form will open, and enables a button in the form, to send it for review, when I click this button, it used to get the value in Cell C5 of the Activesheet and renames it if there are any special characters and creates a copy of this Activesheet to new workbook and saves to a folder path.

    it worked fine for few instances, but when I am trying to test it again, it is throwing me an error

    'Run time error 1004, Copy method of worksheet class failed' and moreover, it is not reading the cell value in C5 of the Activesheet at replacing the special characters.

    Here is the code what I am using

    [code]
    Sub ReplaceSpecialChars()
    Dim SubjArray(0 To 8) As String
    Dim i As Integer, j As Integer
    Dim StrSubj As String

    SubjArray(0) = "\"
    SubjArray(1) = "/"
    SubjArray(2) = ":"
    SubjArray(3) = "*"
    SubjArray(4) = "?"
    SubjArray(5) = Chr(34)
    SubjArray(6) = "<"
    SubjArray(7) = ">"
    SubjArray(8) = "|"

    StrSubj = ActiveSheet.Range("C5").Value
    MsgBox StrSubj 'Not getting any value here, it is showing as blank
    For i = 0 To 8
    StrSubj = Replace(StrSubj, SubjArray(i), " ", 1, -1, vbTextCompare)
    Next
    MsgBox StrSubj 'Not getting any value here, it is showing as blank
    ActiveSheet.Range("C5").Value = StrSubj
    End Sub

    [\code]

    Code for copying the activesheet is shown below
    [code]
    Sub SendToReview()
    Dim Dt As String
    Dim StrFilename As String
    Dim Folder As String
    Dim Fname As String


    Application.DisplayAlerts = False
    Dt = Format(Now, "yyyy-mm-dd hh-mm")
    Folder = "MyPath" & "\"

    Fname = ActiveSheet.Range("C5").Value
    ActiveSheet.DrawingObjects.Visible = False
    ActiveSheet.Copy 'Here it is throwing an error

    StrFilename = Folder & Fname & " - " & Dt & ".xlsx"
    ActiveSheet.SaveAs Filename:=StrFilename
    End sub
    [\code]

    Any help is really appreciated !!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Activesheet Method not working in my Macro

    Are you sure ActiveSheet refers to the sheet you think it does?

    PS When posting code can you please use CODE tags?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    08-30-2012
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Activesheet Method not working in my Macro

    Hi Norie,

    Thank you for looking into my code.

    As I said, when I click on the drawing object which will take me to the home sheet of the macro and enables the second button.

    when i click on this second button, the following code is there before above code is executed.

    [CODE]
    Sheets("Home").Activate
    ActiveSheet.Next.Activate
    [\CODE]

    so, to answer your question, i think the code in above Activesheet is referring to the correct sheet.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Activesheet Method not working in my Macro

    Have you checked the active sheet is the one you want/think it is?

    You can easily do that in the Immediate Window with this.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-30-2012
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Activesheet Method not working in my Macro

    Hi Norie,

    I have checked it in the immediate window. it resulting the correct sheet name that what I want.

  6. #6
    Registered User
    Join Date
    08-30-2012
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Activesheet Method not working in my Macro

    Hi Norie,

    Did you got chance to look into my query ?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro with activesheet reference stops working when worksheet is renamed
    By Naz555 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-20-2017, 01:31 AM
  2. [SOLVED] Using the GetOpenFileName method to import sheets after activesheet
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-27-2014, 07:48 AM
  3. Macro autofill stops working at Column AA? (method global failed)
    By Excel2010101 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-25-2014, 06:48 PM
  4. If activesheet.name not working
    By alexaa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2013, 12:11 AM
  5. ActiveSheet.Name not working?
    By schmidt62 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-29-2012, 05:03 PM
  6. Location method with ActiveSheet
    By pierre_colin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2010, 07:51 AM
  7. [SOLVED] Macro working in background ie not on activesheet
    By Dynamiteskippy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1