No Seet2 in Personal.xlsb ? 2

[jslmvl]

Hi,

This is for Excel.
In VBAProject list -> Personal.xlsb -> Microsoft Excel Objects there are Sheet1 (Sheet1) and ThisWorkbook, where is Sheet2 (Sheet2) and Sheet3 (Sheet3)? Or, can I add Seet2(Sheet2) and Sheet3 (Sheet3)?

I will be appreciated for an help.

 

[combo]

In the VBE project explorer window you can see excel and vba objects that have modules. In excel objects section there is workbook (as thisworkbook) and can be worksheets and chartsheets, depending on workbook's contents.
The 'personal' is a hidden workbook designated to store user's frequently used macros. In your case it means that the 'personal' has only one worksheet, no reason to add any more.

combo

 

[jslmvl]

combo,

Thank you for your response.

For example, if I put following VBA in Personal.xlsb
Sub Macro1()
Sheet1.Columns(1).Cells(1, 1) = Sheet2.Cells(1, 1)
End Sub
I will get error because there is no Sheet2.

 

[PHV]

And this ?
[!]ActiveWorkbook.Sheets("[/!]Sheet1[!]")[/!].Columns(1).Cells(1, 1) = [!]ActiveWorkbook.Sheets("[/!]Sheet2[!]")[/!].Cells(1, 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

PHV,

That works! However, I got two questions then:
1. If users change the sheet name....
2. What is Sheet1 in Personal.xlsb? I found we cannot open it by View Object!

 

[combo]

In This case Sheet1 is a code name of the worksheet you work with. I guess that you don't need to work with 'personal', rather with active workbook.
You can't see the object because the 'personal' workbook is hidden (has hidden window).

combo

 

[SkipVought]

Hi,

Your PERSONAL.xlsb is a HIDDEN WORKBOOK.

Unless you choose to make it visible and use the sheets in this workbook, and I would seriously question WHY you might do that, there is no reason al all to fret regarding the lack of these sheets, as any code that you run, from your PERSONAL.xlsb workbook, will be running in the the VISIBLE workbook, not the PERSONAL.xlsb, HIDDEN workbook, and therefore any reference to a sheet other than sheet1 will be referring to the VISIBLE workbook.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

1)
ActiveWorkbook.Sheets(1).Cells(1, 1) = ActiveWorkbook.Sheets(2).Cells(1, 1)
2)
You have to understand the difference between ThisWorkbook and ActiveWorkbook

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jslmvl]

Thank you for all helps!