-
Sep 5th, 2007, 01:56 PM
#1
Thread Starter
New Member
[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
-
Sep 5th, 2007, 02:02 PM
#2
Re: Set statement
Thread moved to Office Development forum
-
Sep 5th, 2007, 02:57 PM
#3
Hyperactive Member
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.
-
Sep 5th, 2007, 03:38 PM
#4
Junior Member
Re: Set statement
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.
-
Sep 5th, 2007, 03:46 PM
#5
Frenzied Member
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
-
Sep 5th, 2007, 04:22 PM
#6
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
-
Sep 6th, 2007, 02:31 AM
#7
Addicted Member
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.
-
Sep 6th, 2007, 03:10 AM
#8
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
-
Sep 6th, 2007, 07:11 AM
#9
Frenzied Member
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
-
Sep 6th, 2007, 07:23 AM
#10
Frenzied Member
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
-
Sep 6th, 2007, 07:34 AM
#11
Re: Set statement
well i have no problem using codename to return the name into a msgbox or debugprint
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
-
Sep 6th, 2007, 07:42 AM
#12
Hyperactive Member
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.
-
Sep 6th, 2007, 07:56 AM
#13
Frenzied Member
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
-
Sep 6th, 2007, 08:03 AM
#14
Re: Set statement
he did ask where the sheet1 etc come from
but here is the code to do as he requested
vb Code:
Set s = ThisWorkbook.Sheets.Add(, Sheets(Sheets.Count))
s.Name = "results"
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
-
Sep 8th, 2007, 01:23 PM
#15
Re: Set statement
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
-
Sep 21st, 2007, 11:26 AM
#16
Thread Starter
New Member
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.
-
Sep 21st, 2007, 11:27 AM
#17
Thread Starter
New Member
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!
-
Sep 21st, 2007, 11:57 AM
#18
Frenzied Member
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
-
Sep 21st, 2007, 11:59 AM
#19
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
-
Sep 21st, 2007, 03:58 PM
#20
Thread Starter
New Member
Re: Set statement
Wonderful!! Thank you and enjoy your weekend!!
-
Sep 24th, 2007, 03:32 PM
#21
Thread Starter
New Member
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!
-
Sep 24th, 2007, 03:36 PM
#22
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
-
Sep 26th, 2007, 10:42 AM
#23
Thread Starter
New Member
Re: Set statement
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|