• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Who can help me with a macro.

E

Edbrugman

New Member
My problem is that I can't get the Get Data macro to work, but it does work in an example I first made in an empty worksheet.
What am I doing wrong!!
 

Attachments

  • Exsample.xlsm
    59.1 KB · Views: 5
According to - any Excel - forum rules :​
  • you must first edit your thread title according to your need …

  • Edit your initial post to give a better explanation in order there is nothin to guess !
 
If you indicate the reference to your worksheet in your VBA, you always put the " "
The name of your worksheet is never between " "?
 
Edbrugman said:
If you indicate the reference to your worksheet in your VBA, you always put the " "
The name of your worksheet is never between " "?
Click to expand...
It is a matter of string matching. Space characters are important. "Booking " and "Booking" are not the same string. Therefore your code produces an error when you try to reference the worksheet using Worksheets("Booking") because there is no worksheet named "Booking". Only one named "Booking "
 
The reason why it's far ♪ better safer stronger ♫ to work with the worksheet CodeName rather than its name …​
​
 
Wait, "code name" like "Sheet1"? I've never seriously thought about using those; are you saying they never change, once established?

Though in many cases that wouldn't work for me. My clients may carelessly change a sheet name and cause the programs I write for them to fail, but they also need to replace a sheet with a newer one by the same name. For instance, in one instance there are various sheets named "XYZ 2022-Q4" and so forth, but one named "XYZ Current quarter"; I gotta go for that name each time, because it's assigned to different sheets from quarter to quarter.

Or is "code name" something different?
 
Yes, when "Sheet1" is renamed as "Data" its CodeName stays as Sheet1 …​
So this is the best efficient safer secure strong always working way to qualify a worksheet if only​
the VBA procedure is located within the same workbook than the worksheet and the worksheet is not deleted obviously …
▲ click​
​
To test on a brand new workbook :​
Code: 
Sub DemoCodeName()
    Sheet1.Name = "Data"
    MsgBox Sheet1.Name & vbLf & vbLf & Sheets("Data").CodeName
End Sub
Sheet1 is the worksheet CodeName whatever it's name is "Sheet1" or "Data" !​
A CodeName is an object reference like ActiveSheet, ActiveWorkbook, ThisWorkbook, …​
​
CodeName samples threads :​

Split into new workbooks based on column value along with existing macro copied to the new workbooks

Hi Team, I need help to create a vba code that will help me split an existing workbook into multiple new workbooks based on Team (column J) of the attached sample. Along with creating a new workbook, it should also copy any of the existing vba scripts into each of the new workbooks and save...
chandoo.org

VBA to concatenate based on Column header name

Hello Dear , Could you please help me in concatenating the columns based on Column name and not by specific range as the data keep changing every week. Result should be in the Column "Billing" This column should be created after last available column provided. This is the input file Result...
chandoo.org
 
The strange thing is that the macro does work in this example. It only doesn't work in the sheet I had a question about.
 

Attachments

  • Example 2.xlsm
    19.8 KB · Views: 2
Marc L said:
The reason why it's far ♪ better safer stronger ♫ to work with the worksheet CodeName rather than its name …​
​
Click to expand...
You and I are going to have to agree to disagree on that one. I view codenames as the bane of coding clarity and portability, and I have often seen newbies get tangled up because of them. Real, meaningful worksheet names are the better way to go. YMMV.
 
Edbrugman said:
The strange thing is that the macro does work in this example. It only doesn't work in the sheet I had a question about.
Click to expand...

Did you read my earlier reply about this? There is nothing strange about it. The worksheet name in your first example workbook had a trailing space. The 2nd workbook did not. If you cannot be bothered to look for leading or trailing spaces in your worksheet names, then you could also go with something like this.
Code: 
Sub GetData2()
'
' transfer Macro
'
    For Each WS In ThisWorkbook
        WS.Name = Trim(WS.Name)
    Next WS

    Worksheets("Invoice").Range("H15").Value = Worksheets("Booking").Range("I18").Value
    Worksheets("Invoice").Range("H16").Value = Worksheets("Booking").Range("I20").Value
    Worksheets("Invoice").Range("H17").Value = Worksheets("Booking").Range("I22").Value
    Worksheets("Invoice").Range("E21").Value = Worksheets("Booking").Range("I14").Value
    Worksheets("Invoice").Range("H21").Value = Worksheets("Booking").Range("I2").Value
    Worksheets("Invoice").Range("E27").Value = Worksheets("Booking").Range("I6").Value
    Worksheets("Invoice").Range("H27").Value = Worksheets("Booking").Range("I8").Value
    Worksheets("Invoice").Range("M27").Value = Worksheets("Booking").Range("I44").Value
    Worksheets("Invoice").Range("L30").Value = Worksheets("Booking").Range("I10").Value
    Worksheets("Invoice").Range("L36").Value = Worksheets("Booking").Range("I46").Value
    Worksheets("Invoice").Range("H18").Value = Worksheets("Booking").Range("I24").Value
    '
End Sub
 
rlv01 said:
You and I are going to have to agree to disagree on that one
Click to expand...
As it depends on experience, on which context and who are the final users …​
​
Like I wrote, in a pro context - so obviously not for those begging for help for their homework or​
playing with ChatGPT without learning Excel basics neither VBA basics so obviously neither for a 'new VBA' born -​
if - again - the VBA procedure is located in the same workbook than the worksheets,​
if for some reason the workbook can't be protected against users manual operations,​
like for example an user can rename any worksheet as per its will - year change, project name whatever -​
so the best easy efficient safer stronger way is to qualify the worksheets by their codenames obviously rather than their names,​
that avoids some ticket incidents like when the 'technician' comes to the owner computer​
to just see its 'big crucial issue' claimed comes only 'cause the worksheet was renamed !​
​
It is also a way to face an user with its ill will which was warned to not delete a specific worksheet,​
always arguing "that don't work" but the other users do not have any issue, he did not care,​
believe he is so smart in the coffee room telling cracks to other people, so after a meeting​
with his boss demonstrating how bad this user was, never add anymore bad news from him …​
​
Using directly CodeNames avoids to create useless Worksheet variables,​
a bad use seen whatever the forum, even from VBA experts, Gurus, MVP and so on …​
​
Yes obviously working with worksheet name or its index can be easier for 'newbies'.​
But at least a VBA forum can show other ways … ;)​
​
 
You must log in or register to reply here.
Back
Top