Can accidentally having caffeinated coffee at night before taking sleeping pills to rectify it cause insomnia?

Yes, caffeine typically will impair sleeping ability. Use of sleeping pills should be limited at a minimum in general, definitely not to be used to counter the effects of caffeine, best not to use caffeine at night.

Would avoid all caffeinated beverages for at least 6 hrs prior to bedtime and try to avoid any sleeping pills.

Rather than relying on pills, you may wish to try healthy sleep practices. Sleep hygiene: Visit this site for info on this topic https://www.cdc.gov/sleep/about_sleep/sleep_hygiene.html Wish you good health!