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]

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.Worksheets("My Sheet")

No, I think we are trying to say the same thing, but missing it a bit. In the above, the worksheet's name that shows on the worksheet's tab is being referred to. Hence the quotes, as this property is referring to a string.

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?

Ahh, now I think we're on the same track. In the Properties window (number 3), there are two Name properties. The top one (that has the parentheses around the word Name), is the sheet's CodeName. My apologies if I was unclear.

Mark

 

[waldymar]

Ahh, now I think we're on the same track. In the Properties window (number 3), there are two Name properties. The top one (that has the parentheses around the word Name), is the sheet's CodeName. My apologies if I was unclear.

Mark

You are absolutely right! Thanks a lot, Mark. It works, but I wanted to make sure that I got it correctly. The code are gonna use other people so I wish it works smoothly.
Thanks again for your assistance, you helped a lot!