Copy Whole Sheet (with Pics) from one Workbook to another

Hello All,

I am trying to copy a worksheet from one WorkBook to another; with pics.

Thanks for the help

Ernesto

Please Login or Register  to view this content.

TotalSheetNumber must be the string name, or sheet #.
I dont see it assigned anywhere.
I would use string names so you know EXACTLY what sheet its going to.

Sheets(sWs).Select
Sheets(sWs).Copy Before:=Workbooks(sWBook).Sheets(1)

Here is the Complete Sub.

Basically, I am trying to read this Workbook containing different sheets for each Store, and a Total Sheet with Totals for all stores.

All the Stores are to be inserted into One Sheet (in Thisworkbook); with an extra Column for the Store Name (from the Sheet Name). This part works Ok., but I cannt get to Copy the "TOTAL" sheet into Thisworkbook.

NOTE: TargetWB and Thisworkbook point to the same Workbook.
NOTE: JoorWbName is the Text where the Source workbook is located (user would select this from a FileDialog.

Please Login or Register  to view this content.

it does not look as a complete code because:
TargetWB - has no value
Thisworkbook - if it is a variable, it is empty. Plus it's name equals to the Application property name in VBA which is not good.

TargetWb is passed in as a parm; it was initialized prior to the Call to the Sub as follow:

Set TargetWB = ThisWorkbook ' ThisWorkBook is the workbook that contains the macro code.

All goes well until I try to execute the Statements in Red.

I have been trying to Copy / Paste in so many ways. but without success.

This is the Latest

Please Login or Register  to view this content.

Thanks

hi there,

basing on the complete code in post #4 the root of the problem is another instance of Excel which you use to open JoorWbName variable value workbook. Excel can not move/copy sheets between different Excel instances. So instead of

Please Login or Register  to view this content.

it should be

Please Login or Register  to view this content.

and it would work as expected then:

Please Login or Register  to view this content.

Thanks... That was my mistake.

Here is the Working version so far:

Please Login or Register  to view this content.