vba code pasting into the wrong sheet

[wingers13]

Hi,
I'm looking for some advice with a new macro I have made.

I've previously created a number of macros which open files, copy data and then process/paste it into another sheet. This time I'm somehow not able to paste the data into the correct sheet however.

I've trimmed the code down below to show the core issue in a simplified form.
The data is copied from a csv file with only one sheet in it, and then should be pasted into the "Data" sheet in my main workbook. The problem is despite closing the csv file and activating the Data sheet, the data is still pasted into a different sheet in the main workbook.

Any help on this would be much appreciated.
Thanks in advance,
Wingers

    Dim StrFile As String
    Dim Folder As String
    Dim SessionName As String
    Dim Current As Workbook
    Dim csv_file As Workbook
    
    'Set Starting values
    Application.ScreenUpdating = False
    Set Current = ActiveWorkbook
    Folder = Range("Folder").Value
    StrFile = Dir(Folder)
    
    Do While Len(StrFile) > 0
        Workbooks.Open Filename:=Folder & StrFile
        Set csv_file = ActiveWorkbook
        
        'Collect data
        SessionName = Left(StrFile, Len(StrFile) - 4)
        
        csv_file.Close
        Current.Activate
        Sheets("Data").Activate
        
        'Paste data
        Cells(5, 1).Value = SessionName
        
        StrFile = Dir
    Loop
    
    
    Application.ScreenUpdating = True

 

[wingers13]

I should say that this is the same approach I have used successfully in other projects, which makes it all the more confusing...

 

[MARK858]

What happens with

        SessionName = Left(StrFile, Len(StrFile) - 4)
        
        csv_file.Close
        Current.Sheets("Data").Cells(5, 1).Value = SessionName

and is Current the workbook the code resides in?

 

[wingers13]

What happens with

        SessionName = Left(StrFile, Len(StrFile) - 4)
       
        csv_file.Close
        Current.Sheets("Data").Cells(5, 1).Value = SessionName

and is Current the workbook the code resides in?

Yes, "current" is the main workbook with the code in it.
"SessionName" is a variable that is collected from the csv file, trimmed to make it more useful, and pasted into the new sheet.
For now I've just put it into cell A5 as a test, but it doesnt appear on the Data sheet. Instead it appears in the first sheet of the file.

Wingers

 

[welshgasman]

Try a Select instead, if only for debugging?
I would also walk the code.

Sheets("Destinations").Select

 

[RoryA]

For now I've just put it into cell A5 as a test, but it doesnt appear on the Data sheet.

Is that with the code that Mark just posted? That should not be possible.

I suspect your code is in the worksheet module for the sheet where the "paste" is actually happening.

 

[wingers13]

I tried Select and still no difference. When walking through the code the displayed sheet swapped over but the data still went into the first sheet.

Rory hit the nail on the head though. I'd put the code into the sheet module.l by mistake. Now it's moved and all working correctly. Not one I've come across before but every day's a school day.

Thanks gents.

 

[RoryA]

I tried Select and still no difference

Mark's code didn't use select: it fully qualified the Cells property with the relevant worksheet. No need to select or activate.