VBA: Chose placeholder based on conditions whilst copying from one to another worksheet

Hi all,

I'm using VBA to copy/paste content from several worksheets to an overview worksheet based on the value selected (dropdown) in cell C3. The script searches for the value in C3 in the remaining worksheet (tab) names and then copies the content (C3:C) of any found worksheet to cell C14:C on the overview sheet. Working code (placed on overview worksheet) below.

Please Login or Register  to view this content.

So far so good Now, I have the issue that the content I'm copying over contains a placeholder ({1night}), which I would like to replace with another placeholder (one out of the 9 below) depending on the value I specify in cell E5 on the overview worksheet.

{0nights}
{1night}
{2nights}
{3nights}
{4nights}
{5nights}
{6nights}
{7nights}
{8+nights}

Logic:
- If E5 = 0, replace {1night} with {0nights}
- If E5 = 1, replace {1night} with {1night}
- If E5 = 2, replace {1night} with {2nights}
- If E5 = 3, replace {1night} with {3nights}
- If E5 = 4, replace {1night} with {4nights}
- If E5 = 5, replace {1night} with {5nights}
- If E5 = 6, replace {1night} with {6nights}
- If E5 = 7, replace {1night} with {7nights}
- If E5 >= 8, replace {1night} with {8+nights}

This logic should follow after the 'paste' to the overview sheet (so part of the script above).

Is that feasible? Quite new to VBA so any help would be appreciated (cyber beer included)

Many thanks!

Hi Kingo

May I ask, are you making an application for a BB or hotel? Maybe I can help. I am not a great VBA coder but I have made applications for the hotels.

Originally Posted by Mumps1

Where on the overview sheet would that placeholder be? What would the placeholder look like? Would it look like "{1night}" including the brackets?

Hi Mumps! Yes, that's correct!! The placeholder can appear anywhere in the cell (text) - sometimes even multiple times. Would this be an issue? Thanks so much for your help!! 😊

Hi Mumps!

Oh wow! Awesome - that works!! Thanks so much.

Two remaining questions (hope that's ok!):

1) Is there any way to change it to include the condition "If E5 >= 8, replace {1night} with {8+nights}"? At the moment, if I type in for example 10, it would replace {1night} with {10night}. However, the available strings are 1-8 only, so anything that is more than 8 should use {8night}.
2) Is there a way to re-run this bit of the script, whenever the value in E5 is changed (e.g. from 2 to 3)?

Please Login or Register  to view this content.

Asking because I just noticed that once the copy is pasted to the overview page and the {1night} strings are replaced, the replaced string is then not updating when I change the value again in E5 (which makes sense as the script is controlled via C3). So if that replacement bit could also run retrospectively (after the paste) whenever the value in E5 is changed, that'd be perfect!

Thanks so much for the help! Appreciate it

Hi Mumps,

Thanks so much for this! Tried it, but unfortunately no luck. It doesn't seem to do anything anymore :-(
I've uploaded an example (using the code you had previously provided, which still works). Hope it's easier to work with that?

Many thanks for your help!

Awesome!!! That did the trick! Yaaaay!

Thanks so much for your help. May my day! I owe you a cyber beer *cheers*.

Have a great Christmas!