excel vba - Work with the references of different workbooks -

the idea of code launch workbook called "my list.xlsx" , create list on sheet "fx" of list; list based on spreadsheet of opened workbook called "daily prices.xlsm".

when try play around, seems doesn't way reference list on other workbook copy it. here code:

sub foreachws()     dim ws worksheet, dest worksheet     dim lastrow long     dim lastrowdestination long     dim exratewb workbook     dim dailyprices workbook      set exratewb = activeworkbook     set dailyprices = workbooks("daily prices.xlsm")     set dest = worksheets("fx")      each ws in dailyprices.worksheets         select case ws.name             case "fx", "bbg prices", "prices"              case else                  msgbox dailyprices.name                  lastrow = ws.usedrange.rows.count                 lastrowdestination = dest.usedrange.rows.count + 2                  dailyprices.ws.range(cells(1, 1), cells(lastrow, 5)).copy                 exratewb.dest.cells(lastrowdestination, 1).pastespecial paste:=xlpastevalues, operation:=xlnone, _                                      skipblanks:=false, transpose:=false          end select     next end sub 

as kaybee99 mentioned, don't indicate issue occurs, see couple of potential areas error i'll note above offending line:

sub foreachws()     dim ws worksheet, dest worksheet     dim lastrow long     dim lastrowdestination long     dim exratewb workbook     dim dailyprices workbook 

the assumption pointing @ workbook in code resides. however, there no guarantee of still case, since other code (not shown in op) may have used <workbook>.select have changed activeworkbook is

    set exratewb = activeworkbook 

using method set "workbook" variable better, because it's guaranteed accurate no matter workbook active - preferred method of assigning workbook variables.

    set dailyprices = workbooks("daily prices.xlsm") 

your worksheet assumed in activeworkbook, again, no guarantee whatever active workbook have fx worksheet. recommend changing set dest = exratewb.worksheets("fx") or set dest = dailyprices.worksheets("fx") appropriate

    set dest = worksheets("fx")      each ws in dailyprices.worksheets         select case ws.name             case "fx", "bbg prices", "prices" 

i'd suggest putting @ least comment in here acknowledging intentionally doing nothing these 3 worksheets. helps prevent concern next time (or else) through code & wonders accidentally deleted...

            case else                 msgbox dailyprices.name                 lastrow = ws.usedrange.rows.count                 lastrowdestination = dest.usedrange.rows.count + 2 

dailyprices workbook variable containing collection of worksheets you've referenced in for each... statement created loop you're in. referencing again invalid. ws reference current (in loop) worksheet in dailyprices workbook. remove dailyprices. reference below

                dailyprices.ws.range(cells(1, 1), cells(lastrow, 5)).copy 

similar above comments, dest has been defined worksheet belonging specific workbook, exratewb qualifier unnecessary and, in fact, invalid. remove it.

                exratewb.dest.cells(lastrowdestination, 1).pastespecial _                            paste:=xlpastevalues, operation:=xlnone, _                             skipblanks:=false, transpose:=false          end select     next end sub 

if these changes don't address issue, please specify you're getting error, , error is.