ThisWorkbook paste

[waldymar]

Dear all,

I'm trying to copy a data from the open file into the code-running file:

myFile = varPath & varFileName
                                
    Set wb = Workbooks.Open(myFile)
    Set tbl = wb.Worksheets(varSheetName).Range(varRangeName)
    
    tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy
    
    ThisWorkbook.Worksheets(calc).Cells(y + 1, 1).PasteSpecial

There is a problem with the last row. "calc" is the property name of the sheet in the ThisWorkbook. How can I fix it?

Thanks in advance

 

[GTO]

If by 'property' name, you mean the name on the sheet's tab, put quotes around it.

 

[waldymar]

no, property name means vba name of the sheet and not excel one

 

[GTO]

Okay, that is called the CodeName. Try:

<font face=Courier New>calc.Cells(y + 1, 1).PasteSpecial</FONT>

 

[waldymar]

thanks, it works.

question in order to understand: shouldn't i specify the workbook as i have 2 of them open? what if the code will look for the sheets name (calc) in the wrong workbook?

 

[GTO]

no, property name means vba name of the sheet and not excel one

Just a note of clarification to help. A worksheet has many properties, such as the CodeName property, the Cells property, and of course, a Name property.

thanks, it works.

question in order to understand: shouldn't i specify the workbook as i have 2 of them open? what if the code will look for the sheets name (calc) in the wrong workbook?

Well, think of the CodeName as an Object name, which in this case, said object belongs to the workbook in which it resides. You could think of this like ThisWorkbook. Since it belongs to the specific workbook, Excel knows what it belongs to when you write ThisWorkbook.Woksheets("My Sheet").

Similarly, if you added a UserForm and named it 'frmDataForm', you would just use frmDataForm to refer to it.

Does that make sense?

Mark

 

[GTO]

BTW, you may wish to use some type of consistent naming convention for such objects. Something like shtData or wksData for a sheet's codename, or frmDataEntry for a userform, will save you headaches later when you go back to edit/update code you wrote a few months previous.

 

[waldymar]

It does, thanks a lot for clarification!

 

[GTO]

You are most welcome and happy to help

 

[waldymar]

I think I realize that we spoke about different names. Codename you were talking about is probably something like:

Dim calc as a worksheet
set calc=ThisWorkbook.Woksheets("My Sheet")

I meant a different sheet name:
when you are in VBA window it has 3 sections:

1. the main one where you write code
2. project VBAProject
3. properties

if you go to properties attributed to your worksheet then you can give a vba name to it. it prevent code working from the sheet name change in excel.

what do you think?