To select an Input Sheet to Copy

[sreeks]

Hi All,

The below code was picked up from this forum and one of this forum members has helped in copying the Value and Format and placing it in the new sheet.

I am trying to select a sheet to be copied via macro which is not working. I am absolutely new to coding and request the team to excuse me if there is a silly coding mistake.

Below is the objective:

The macro should give a dialogue box to select the Input Sheet to be copied rather than explicitly specifying "Sheet Name" in the code

Best Regards,
Srikanth

Please Login or Register  to view this content.

Would like to implement the above in the below code:

Please Login or Register  to view this content.

[Marcol]

Hi Srikanth this effectively a double post.

http://www.excelforum.com/excel-prog...e-a-sheet.html
Although you have marked this thread [Solved], you have left it open ended with the same question you are asking here!

Please go back to your last thread and delete the hanging question.

from

However, what I am also looking in addition to that is as below:......

Cheers

[sreeks]

Hi Marco,

I have updated the previous post and this would be an independent thread.

Best Regards,
Sri

[sreeks]

Hi Photon,

I am trying to use the logic in the code provided by Marco.
I am unable to execute the code since by default the macro is showing the message "&sPic&" cannot be found.

My question is if we are using sName as string variable and this is being used as the sheet name, then the same way sPic should be a string variable to recognize the existing Sheet Name.

Best Regards,
Sri

Please Login or Register  to view this content.

[protonLeah]

Umm, is this the right thread?? I'm confused (not again! )
--------------------------------------------
sPic is not a string, it is a variable holding a string value:

Please Login or Register  to view this content.

[sreeks]

Hi Photon,

Sorry for the confusion.
I had to close the open ended question in the previous thread since that thread was marked as "Solved".

I changed the code as per your advise and ran it. By default it throws the error message "Sheet cannot be found.

After this message, the message "Enter the New Sheet Name" is popping up.

Below is the code for your reference:

Please Login or Register  to view this content.

What I also did was I deleted the below section from the code:

Please Login or Register  to view this content.

Now the error is

"Run Time Error '1004'.
PasteSpecial Method of Range class failed

Regards,
Sri

[protonLeah]

Sorry again! The lines I provided were only meant to replace the original lines, leaving all others as is:
(tested: works)

Please Login or Register  to view this content.

[sreeks]

Hi Proton,

The code works fine, just that I would like to copy only the Format and Values and not formula from the Input Sheet.

Regards,
Sri

[sreeks]

Hi Proton,

I am half through the problem which I am doing manually at the moment.

I was referring to the below objectives:
1. Would like to copy only the Format and Values in the new sheet
2. Would like to copy only certain portion of the Input Sheets and not the entire sheet.

Attached is for your reference.

Regards,
Sri