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]

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

code posted opens workbook in specified folder , copies the required range & pastes to destination range in the destination workbook - solution all works ok
If you have made changes to code I posted then will need to see what you have done.

Dave

 

[jmoney30]

Sorry but I used your same exact code and it doesn't work

 

[jmoney30]

Thanks I think I figure it out.

 

[dmt32]

Sorry but I used your same exact code and it doesn't work

As published, code works ok for me - In #Post 6 you stated you made some changes? without seeing what you are doing a little difficult to determine what your issue is.

If can, place copy of the workbook containing the macro on a file sharing site like dropbox.

Dave

 

[jmoney30]

I save my vba code in a personal file. If I write code in this file and run it updates the current active workbook. With this code I have to paste this code in the actual workbook for it to work as if it’s not recognizing the active book. Why is this and is it something I can change. The only updates I made to the code was the necessary file path and sheet name.

 

[dmt32]

I save my vba code in a personal file. If I write code in this file and run it updates the current active workbook. With this code I have to paste this code in the actual workbook for it to work as if it’s not recognizing the active book. Why is this and is it something I can change. The only updates I made to the code was the necessary file path and sheet name.

try changing this line

 Set PasteRange = ThisWorkbook.Worksheets("Sheet1").Cells(4, 1)

to this

Set PasteRange = ActiveWorkbook.Worksheets("Sheet1").Cells(4, 1)

and see if resolves your issue

Dave

 

[jmoney30]

Now I’m getting a runtime error 9 subscript it out of range. The data is being copy all I have to do is right click and paste. I think it’s something with the cells(4,1). I tried range and out cells but keep getting error

 

[jmoney30]

I got it to work thanks for the help

 

[smozgur]

Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers.