Post from close book to an open

[jmoney30]

I'm trying to use this code but it will not paste the information. It opens the close book and selects but doesn't paste.

Sub GetDataClosedBook3()





Dim sourceworkbook As Workbook

'location of the file and data to copy

Dim currentworkbook As Workbook



Set currentworkbook = ThisWorkbook

Set sourceworkbook = Workbooks.Open("C:\Users\June.xlsx")

sourceworkbook.Sheets("Sheet2").Range("A2:C11").Copy



currentworkbook.Activate

currentworkbook.Worksheets("Sheet1").Activate

currentworkbook.Worksheets("Sheet1").Cells(4, 1).Select

ActiveSheet.Paste



sourceworkbook.Close

Set sourceworkbook = Nothing

Set currentworkbook = Nothing



ThisWorkbook.Activate

Worksheets("Sheet1").Activate

Worksheets("Sheet1").Range("A1").Select





End Sub

 

[dmt32]

Hi,
welcome to forum

Try this update to your code & see if does what you want

Sub GetDataClosedBook3()
    Dim SourceWorkbook As Workbook
    Dim PasteRange  As Range
    Dim FileName    As String
    
    FileName = "C:\Users\June.xlsx"
    
    If Dir(FileName, vbDirectory) = vbNullString Then
        MsgBox FileName & Chr(10) & "File Not Found", 48, "Not Found"
    Else
        Application.ScreenUpdating = False
        Set PasteRange = ThisWorkbook.Worksheets("Sheet1").Cells(4, 1)
        
        Set SourceWorkbook = Workbooks.Open(FileName, False, True)
        
        SourceWorkbook.Sheets("Sheet2").Range("A2:C11").Copy PasteRange
        
        SourceWorkbook.Close False
        Application.ScreenUpdating = True
    End If
    
End Sub

Dave

 

[jmoney30]

Thanks,
I"ll try it out

 

[jmoney30]

I tried and it still doesn't work

 

[dmt32]

I tried and it still doesn't work

Code works fine for me

does not work it what way?
Did you make any changes to published code and are you getting any errors?

Dave

 

[jmoney30]

I made some updates and still not working. I don't believe its copying the data. when I open the sheet the data is selected not copied

 

[jmoney30]

No errors and no

 

[dmt32]

I made some updates and still not working. I don't believe its copying the data. when I open the sheet the data is selected not copied

code I posted does not select the data - post code you have updated

Dave

 

[jmoney30]

It opens and works put doesn't paste. I have it going to an existing workbook with other sheets in it. I keep the workbook open when running and nothing paste. Its is 2016.

 

[jmoney30]

I thought it should copy data from a closed workbook and paste to a workbook that's open that I'm working on.