Results 1 to 23 of 23

Thread: [RESOLVED] Set statement

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Posts
    7

    Resolved [RESOLVED] Set statement

    My workbook contains one and only one worksheet named “Original”. I need to insert another worksheet "Result", then copy and paste data from “Original”, and work on the new worksheet "Result".

    I am trying to understand the following codes:

    Dim source as worksheet
    Dim target as worksheet
    Set source = sheet5
    Set target = sheet2
    Dim i as integer
    ……

    What I don’t understand is, where do “sheet5” and “sheet2” come from? Are they real worksheet names? What are they if not? I tried to change these names to others like “sheet4” or “sheet6”, then I will receive wrong messages. If I change to "Set source = Original Set target = Result", then the program doesn't work. Really confusing.

    Thank you!
    Last edited by Hack; Sep 26th, 2007 at 10:44 AM. Reason: Added RESOLVED to thread title and green resolved checkmark

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Set statement

    Thread moved to Office Development forum

  3. #3
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: Set statement

    Where is the code coming into play at? do you have to incorporate "Result" into that code or something?
    Swoozie
    Somedays you just should not get out of bed.

  4. #4
    Junior Member
    Join Date
    Aug 2007
    Posts
    17

    Re: Set statement

    Quote Originally Posted by Potato_2008
    I need to insert another worksheet "Result", then copy and paste data from “Original”, and work on the new worksheet "Result".
    You can also copy, paste, and rename worksheets. In which case you wouldn't be using the Set statement, and you wouldn't need to copy cells from one sheet to the other. I think it goes something like this:

    Code:
    Worksheetname.copy '//insert your worksheet name
    Worksheetname.paste
    Then you can rename the new worksheet to Result. I'm not sure how the code goes -- any help?
    Last edited by Schatzy; Sep 5th, 2007 at 03:41 PM.

  5. #5
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Set statement

    I hope this helps ...
    Code:
    Option Explicit
    
    Sub Macro1()
        Dim shtSource As Worksheet  'Handle for Source Sheet "Original"
        Dim shtTarget As Worksheet  'Handle for Target Sheet "Result"
    
        'Set a HANDLE for the existing sheet
        Set shtSource = Sheets("Original")
        
        'Create a new WorkSheet with a HANDLE "shtTarget"
        Set shtTarget = ActiveWorkbook.Worksheets.Add
        'Name the New Sheet to "Result", assuming that sheet name is not already used
        On Error GoTo ERR_EXISTS
            shtTarget.Name = "Result"
        On Error GoTo 0
    
        'Continue here ...
        
        'TEST TEST TEST TEST
        MsgBox shtTarget.Name
        'END TEST
        
        Exit Sub
    
    'Error Exception Handler
    ERR_EXISTS:
        Dim mes1 As String, mes2 As String, stat As Integer
        'PopUp on Error renaming sheet
        mes1 = "ERROR: SHEET ""RESULT"" ALREADY EXISTS"
        mes2 = "Can't name new sheet ""Result"" - that sheet already exists."
        stat = MsgBox(mes2, vbOKOnly, mes1)
        'Fatal Error - Exit the Macro
        End
    
    End Sub
    If you need help understanding any of this, just ask.
    Last edited by Webtest; Sep 5th, 2007 at 03:58 PM.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Set statement

    What I don’t understand is, where do “sheet5” and “sheet2” come from
    sheets in a workbook are given a CodeName Sheet#, when they are created, this is separate from the woksheet name, the CodeName is a readonly property and afaik can not be changed
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Addicted Member
    Join Date
    Jan 2007
    Location
    England
    Posts
    234

    Re: Set statement

    the codename can be changed

    tried to upload an image showing this as i wasnt sure how to do it

    edit: just found this

    http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

    wont load for me in work for some reason, so could be something completely different lol
    Last edited by Mitch_s_s; Sep 6th, 2007 at 02:39 AM.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Set statement

    i tried to change by code, just error "read only"
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Set statement

    It is obviously an obscure "System Assigned Handle" for the object that is assigned by the system when the object is created. I've never heard of it until now, but I notice that the CodeName seems to be the part of the sheet reference to the left of the opening parenthesis in the VBAProject pane in the Microsoft Visual Basic window.
    Code:
    Sub Macro1()  'On a Fresh Workbook with only 1 Sheet
    
        MsgBox Sheets("Sheet1").CodeName  ' > "Sheet1"
    
        Sheets.Add.Name = "MyJunk"
    
        MsgBox Sheets("MyJunk").CodeName  ' > "Sheet2"
    
    End Sub
    I would say ignore it and assign your own handle with a meaningful name and use that instead. NOTE that Excel NEVER reuses one of these CodeName handles as long as it continues to run, even if you delete the sheet. I have a project that creates 160 sheets, and while I am debugging, if I delete all those sheets and run the project again, it will crash after "Sheet256" when it tries to create the sheets again, because (my version of) Excel is limited to 256 sheets.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  10. #10
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Set statement

    Well, the CodeName is not really a "Handle" ... if you do a:

    Dim aSht as Worksheet

    And Set it to a valid worksheet, you can do the following:

    MsgBox aSht.Name

    But if you have a Code Name "Sheet1" you can NOT do the following:

    MsgBox Sheet1.Name

    It gives you a very unusual error message with no "Debug" button and no line highlighting when you try to execute it! I would absolutely NOT use the CodeName. Let the system keep it to itself.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Set statement

    well i have no problem using codename to return the name into a msgbox or debugprint
    ?sheet1.Name
    RESULTS
    it gives an object required error if no sheet with that codename exists

    the good thing about using codename, is that all the codenames are in the intellisense and makes it easy to know what properties and methods are available without setting the sheet to a sheet variable
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12
    Hyperactive Member
    Join Date
    Aug 2001
    Location
    Washington DC
    Posts
    330

    Re: Set statement

    You guys, hate to interject but I think we are getting ahead of ourselves here and need to get a response from the actual user. For all we know from the original post is that he may need to add a third sheet. His exisiting code didnt mention anything about hidden sheets, or create sheets, or even if the original code did work correctly until he started making changes.

    The codename comes in very handy especially if you have users that can not leave well enough alone and like changing the worksheet names.
    Swoozie
    Somedays you just should not get out of bed.

  13. #13
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Set statement

    Westconn1:

    If you add or delete sheets, things get very confusing very quickly. If you save a workbook with only Sheet1 and the following macro, the sheet created in line 2 will NOT be Sheet2 as would normally be the case ... it gets created as Sheet3! If you change the "Sheet2" in the last line to "Sheet3" and save the workbook with only Sheet1, the next time you start the file and run the macro it will create Sheet4! It really does some strange things, and I would never try to rely on it.
    Code:
    Sub Macro1()
        Dim aSht As Worksheet
        
        MsgBox Sheets(1).Name & " " & Sheets(1).CodeName
        Sheets.Add.Name = "TEST"
        Set aSht = Sheets("TEST")
        MsgBox aSht.Name & " " & aSht.CodeName
        MsgBox Sheet2.Name
    
    End Sub
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Set statement

    he did ask where the sheet1 etc come from
    but here is the code to do as he requested
    vb Code:
    1. Set s = ThisWorkbook.Sheets.Add(, Sheets(Sheets.Count))
    2. s.Name = "results"
    3. Sheets("original").Copy s
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Set statement

    Quote Originally Posted by Potato_2008
    My workbook contains one and only one worksheet named “Original”. I need to insert another worksheet "Result", then copy and paste data from “Original”, and work on the new worksheet "Result".

    I am trying to understand the following codes:

    Dim source as worksheet
    Dim target as worksheet
    Set source = sheet5
    Set target = sheet2
    Dim i as integer
    ……

    What I don’t understand is, where do “sheet5” and “sheet2” come from? Are they real worksheet names? What are they if not? I tried to change these names to others like “sheet4” or “sheet6”, then I will receive wrong messages. If I change to "Set source = Original Set target = Result", then the program doesn't work. Really confusing.

    Thank you!
    Hi just three questions....

    1) Where did you get this code from?
    2) Have you checked if Sheet2 and Sheet5 are not hidden. you can check by clicking Format=>Sheets=>Unhide
    3)Third question is based on the first two questions. If the answer to the 1st question is "some tutorial" and to the 2nd is "No", then what i understand from your query is that in an excel workbook, you have a sheet called "Original" and you want to insert a sheet "Result".

    Here is the code to insert a sheet

    Code:
    Sub AddSheet()
    Dim wSht As Worksheet
    Dim shtName As String
    
    'Name of the Sheet you want to insert
    shtName = "Result"
    
    For Each wSht In Worksheets
        'Check if any sheet with the above name exists
        If wSht.Name = shtName Then
            MsgBox "Sheet already exists...Make necessary " & _
                "corrections and try again."
            Exit Sub
        End If
    Next wSht
        'Finally Add the sheet
        Sheets.Add.Name = shtName
    End Sub
    Also you want to copy data from sheet "Original" to Sheet "Result". If that is correct then the question is what do you want to copy? all data in the sheet or just specific cells?

    Hope this helps...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  16. #16

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Posts
    7

    Re: Set statement

    I apologize for my late reply because I wished to come back with a result. I carefully read through each of your posts and sincerely appreciate all your kind inputs! Thank you very much!

    I didn't explain well in my op, and I am actually working on macro/vb in excel environment. I finally find out where those "sheet2" and "sheet5" come from... they are generated by excel automatically and the real worksheet names are put in brackets right behind them. If you open excel and click Tools/Macro/Visual Basic Editor, then in the new vb window, you will see them under Project-VBAProject/VBAProject(filename.xls)/Microsoft Excel Objects/Sheet2 (Original) and .... /Sheet5 (Result). Maybe I inserted and deleted some blank sheets and forgot what I did, thats why they showed up as Sheet2 and Sheet5 instead of Sheet 1 and Sheet2.

    I am brand new in using vb and I do need your help! To make it clear, I would put my new question in a new post.

  17. #17

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Posts
    7

    VB Rows statement

    I hope to insert 3 blank rows between row(i) and row(i+1), where i is uncertain and supposed to change. I tried to use:

    Rows("i+1:i+3").Select
    Selection.Insert shift:=x1down

    The above codes doesn't work. But it will work well if I change variable i to some certain numbers, eg.

    Rows("14:16").Select
    Selection.Insert shift:=x1down

    How should I adjust my expression?

    Thank you!

  18. #18
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Set statement

    Range(Cells(i,"A"),Cells(i+3,"A")).EntireRow.Select

    If you are working with multiple sheets you may have to include the sheet name before the "Range" AND before each of the "Cells", and if this is in a function, rarely you have to use the numeral for the Column number instead of the letter ("1" instead of "A").

    You can get rid of the "Selection" ... just append ".Insert shift:=xlDown" in place of ".Select" in the first line.

    Hope this helps you.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Set statement

    The problem there is that you put the variable (and +x ) inside the quotes, so you were actually trying to use the rows called "i+1" and "i+3".

    It should be like this instead:
    Code:
    Rows(i+1 & ":" & i+3).Select
    For further info, you don't actually need two lines - you can eliminate the .Select and Selection, leaving this:
    Code:
    Rows(i+1 & ":" & i+3).Insert shift:=xlDown

  20. #20

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Posts
    7

    Re: Set statement

    Wonderful!! Thank you and enjoy your weekend!!

  21. #21

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Posts
    7

    Range statement

    Sheets("Sheet1").Select
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R3C1:R14C2,2,FALSE)"
    Selection.AutoFill Destination:=Range("C3:C14"), Type:=xlFillDefault

    I need to exchange C14 with the last row of the used range in sheet1, which is unknown in advance. I tried the following, but doesn't work.

    Selection.AutoFill Destination:=Range("C3:C & Sheet1.UsedRange.Rows.Count "), Type:=xlFillDefault"

    How to correct my fomula please? Thanks!

  22. #22
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Set statement

    The problem is the same as before - you put code inside the string, rather than appending to the string.

    It should be like this:
    Code:
    Selection.AutoFill Destination:=Range("C3:C" & Sheet1.UsedRange
    .Rows.Count), Type:=xlFillDefault

  23. #23

    Thread Starter
    New Member
    Join Date
    Aug 2007
    Posts
    7

    Thumbs up Re: Set statement

    Quote Originally Posted by si_the_geek
    The problem is the same as before - you put code inside the string, rather than appending to the string.

    It should be like this:
    Code:
    Selection.AutoFill Destination:=Range("C3:C" & Sheet1.UsedRange
    .Rows.Count), Type:=xlFillDefault
    That works great, thank you!!

Posting Permissions

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



Click Here to Expand Forum to Full Width