Frustrated with excel vba not working

[jvoss]

Frustrated at below code and any code. i have tried over 1000 ways to do one simple thing. to move data from 1 source workbook to thisworkbook.worksheet("summary") workbook. the below code is last ditch effort to get this to work ( over 50 hours trying to get the ( to work.)

now the code very simple
what is wrong?????????????????????????????????????????????????????????????????????????????????????????

Sub ReadDataFromCloseFile()
    On Error GoTo ErrHandler
    Dim FileToOpen As Variant

 'Application.ScreenUpdating = False
    
    Dim src As Workbook
    
    ' OPEN THE SOURCE EXCEL WORKBOOK IN "READ ONLY MODE".
   
                                 
                                 FileToOpen = Application.GetOpenFilename _
                                 (Title:="Browse for your File & Import Range", _
                                 FileFilter:="Excel Files (*.xls*),*xls*")
    'If FileToOpen <> False Then '<------------------ disable when enabled it verifs a nill select is not passed.
     "end if
    
 Set src = Workbooks.Open(FileToOpen)            ' Set src = Workbooks.Open("C:\testbook.xlsm", True, True)
    
    ' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).row).Rows.Count
  MsgBox iTotalRows '<------------- verify count of i matches with original files.
    ' COPY DATA FROM SOURCE  TO THE DESTINATION WORKBOOK.
    Dim iCnt As Integer         ' COUNTER.
    For iCnt = 1 To iTotalRows
     Worksheets("Sheet1").Range("B" & iCnt).Formula = src.Worksheets("Sheet1").Range("B" & iCnt).Formula
  MsgBox src.Worksheets("Sheet1").Range("B" & iCnt).Formula '<-------------------------verify what is to be pasted in destination file (thisworkbook.)
    Next iCnt
    ' CLOSE THE SOURCE FILE.
    src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[MARK858]

so i have to use code name not tab names for this to work.

Are there any other new requirements other than the above (which is quite a major one) that I need to code for? and what is the sheets codename?

 

[jvoss]

Mark. the OP was the only part i could not get to work. the user form and others are all working good.

the past after the last row was an after thought and a nice to have.

 

[MARK858]

the past after the last row was an after thought and a nice to have.

and I still want the source sheets code name before I code for the above (especially as you aren't coding for the sheets codename in the OP).

 

[jvoss]

Mark, the Code name dataset1<---- has been consistnet for the last 3 months. and tabnames latest is "dataset20APR20"

 

[jvoss]

i was just going to use data1 with out quotes. is this a change from

thisworkbook.worksheet.sheet1 this is for the code name?
or
is thisworkbook.worksheet.("Sheet1") for the code name?

 

[MARK858]

thisworkbook.worksheet.sheet1 this is for the code name?

ThisWorkbook refers to the workbook that the code resides in which in your case is the destination sheet whereas I think you were stating that it is the source workbook that want referring to by it's codename.
Is this correct or is it the destination sheet that you want referring to by it's codename?

If it is correct then you need an entirely different syntax to what you have above.

I will wait until you confirm the above before posting any code

 

[jvoss]

have sample files if you want.

 

[MARK858]

No I just want the question in my last post answered as it is totally different coding depending on the answer.

 

[jvoss]

ThisWorkbook refers to the workbook that the code resides in which in your case is the destination sheet whereas I think you were stating that it is the source workbook that want referring to by it's codename.
Is this correct or is it the destination sheet that you want referring to by it's codename?

If it is correct then you need an entirely different syntax to what you have above.

I will wait until you confirm the above before posting any code

the photos are from source/readonly

the code you provide reads this and past there i guss is's the "active workbook"

is the answer framed right? source is readonly (taking data from)(better if not opened at all) --- destination is to be pasted/copied to.

active is a relevant term as i understand. ie in focus or out of focus

 

[MARK858]

I'll try again..
Which workbook is the sheet that has the codename dataset1 that you want referring to on? the source workbook or the destination workbook (I am guessing by what is in the properties window it is the source workbook, but I am not going to guess. I need a straight answer not an image please)?