Copy data from dynamic named workbook

I have an open workbook "Stock Analysis.xlsm. I need a macro in it that will open another dynamic, (Cell B1 in Stock Analysis), workbook and copy data from the "Summary" worksheet and paste it into the worksheet "Summary" in the Stock Analysis workbook. Once copied then close down the dynamic worksheet.

Cell B1 = TLS
file to open TLS.xlsx

I have tried fgor hours attempting this.



Sub OpenCopyPaste()


Workbooks.Open "C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1" & i)


ActiveWorkbook.Worksheets("Summary").Range("A1:P2").Copy


Workbooks("Stock Analysis.xlsm").Worksheets("Summary").Range("a22:d22").PasteSpecial Paste:=xlPasteValues

End Sub

This my explain better

"Sub OpenCopyPaste()

'open dynamic workbook
Workbooks.Open "C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1" & .xlsx)

'activate dynamic workbook and copy rage of cells
ActiveWorkbook.Worksheets("Summary").Range("A1:P2").Copy

'paste copied cells from dynamic workbook into another workbook which is already open
Workbooks.Activate("Stock Analysis.xlsm").Worksheets("Summary").Range("a22:d22").PasteSpecial Paste:=xlPasteValues

'close dynamic workbook
Workbooks.Close "C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1" & .xlsx)



End Sub"

Try activating your stock analysis workbook before the copy;

Please Login or Register  to view this content.

its not opening up the variable file

You are missing the quotes on ".xlsx"

Compile error

"Sub OpenCopyPaste()

'open dynamic workbook
Workbooks.Open "C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1" & ".xlsx"

You need to close your brackets!

Workbooks.Open "C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1") & ".xlsx"

doesnt like this line


Workbooks.Close "C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1") & ".xlsx"

Did that work? If not try this code. This is the way I'd do it.

Please Login or Register  to view this content.

not liking "Set sourceBook line." the Line

The range B1 is on worksheet "Stock A" could that be the problem

Sourcebook= Nothing ?????

got it running up to copy. its just not pasting or closing source file

PHP Code: 

Sub OpenCopyPaste()

Dim sourceBook As Workbook

'open dynamic workbook
Set sourceBook = Workbooks.Open("C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1") & ".xlsx")

'activate dynamic workbook and copy rage of cells
sourceBook.Worksheets("Summary").Range("A1:P2").Copy

'paste copied cells from dynamic workbook into another workbook which is already open
Workbooks("StockAnalysis.xls").Activate

ActiveWorkbook.Worksheets("Summary").Range("a22:d22").PasteSpecial Paste:=xlPasteValues

'close dynamic workbook
sourceBook.Close

End Sub 


got it to work. it was not the program but my set-up

PHP Code: 

Sub OpenCopyPaste()

Dim sourceBook As Workbook

'open dynamic workbook
Set sourceBook = Workbooks.Open("C:\Users\61458\Desktop\TEST\Windows32\data\" & Range("B1") & ".xlsx")

'activate dynamic workbook and copy rage of cells
sourceBook.Worksheets("Summary").Range("A1:P2").Copy

'paste copied cells from dynamic workbook into another workbook which is already open
Workbooks("Stock Analysis.xlsm").Activate

ActiveWorkbook.Worksheets("Summary").Range("a22:d22").PasteSpecial Paste:=xlPasteValues

'close dynamic workbook
sourceBook.Close

End Sub 


Thank you Croweater and also thank you for your patience

No worries. Glad you got it working.