-3

I want to first apologize, if this question is out there but I have spent countless of hours doing research and trying to apply some VBA codes that some have already put out there. None have worked.

I have an Excel File which I have applied some conditional formatting. I want to be able to copy a text either from a program or file and paste in the Excel file. However, when I paste, the conditional formatting color, font size, font, is lost.

I know one way is to just paste the values. I want to simplify this for the end user and dummy proof it and make it to where whenever they paste either by CTRL-V or right click and Paste, it automatically does it for them. Can this be done in VBA?

braX
  • 11,506
  • 5
  • 20
  • 33
Michael Martinez
  • 1
  • How about just a button that pastes only the values. They copy from the other program, and then hit the button in the spreadsheet. That subroutine would need just one line like `Sheet1.Range("A1").PasteSpecial xlPasteValues` – JNevill Dec 03 '18 at 17:03
  • Hello and welcome to [so]! Please take the [tour] and read through the [help] to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue. – FreeMan Dec 03 '18 at 17:24
  • 1
    To be honest, I'm not even sure what you're after. "However, when I paste, the conditional formatting color, font size, font, is lost." sounds like you want to **keep** the formatting, while "I know one way is to just paste the values" sounds like you want to **drop** the formatting.... – FreeMan Dec 03 '18 at 17:25
  • Right-click and paste isn't "paste special / values", it's plain old `Range.Paste`, which copies the value, formula, borders, font, forecolor, backcolor, conditional formatting, data validation, everything. Please [edit] to clarify what you're trying to achieve, how you're trying to solve the problem, and where/how it's failing – Mathieu Guindon Dec 03 '18 at 17:28

1 Answers1

0

For this question i use:

Sheet1.Range("A1:A5") => This range includes the data to be copy.

Sheet1.Range("B1:B5") => Where the data will copy.

Try:

Option Explicit

Sub Macro1()

     Sheet1.Range("A1:A5").Copy Sheet1.Range("B1:B5")

End Sub
Error 1004
  • 7,877
  • 3
  • 23
  • 46