0

Hi I have a question about clearing and pasting a selection from another workbook.

I am trying to make a macro that clears the old data I have in a tab and copies the data I have on my clipboard from another excel workbook in it's stead.

I kept getting an error before I noticed what was wrong. When the macro runs the clearing part, it cancels my selection/copy data I had, so the pasting part does not work. does anybody know how to circumvent it? I can't reference the excel where the data is coming from directly because it varies every time.

the code I use now for the clearing:

Sub clearData()

Worksheets("ZRFI08TW").Range("A5:M5000").ClearContents

End Sub

The coded i use now for the pasting of the data (i know its not the best, but trying different things before i noticed the conflict ended up with this one):

Sub copyData()

ActiveSheet.Range("A5").Select
SendKeys "^v"
     
End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
perbrethil
  • 131
  • 1
  • 12
  • Whatever you do, probably will clear selection. You could paste the data into a new workbook, clear, and then copy/paste the data from new workbook into destiny, and close new workbook without saving changes – Foxfire And Burns And Burns Oct 21 '20 at 11:32
  • 1
    Why don't you copy, paste, and clear in the end? Think if any other order would be possible. – Pᴇʜ Oct 21 '20 at 11:41
  • Why does it have to be exactly copy, clear, paste? If you can explain more the background, maybe there is a workaround for that. – Pᴇʜ Oct 21 '20 at 13:15

1 Answers1

1

I can come up with two options, following the logic of your code for using SendKeys "^v" for a paste:

  • Using - Worksheets("ZRFI08TW").Range("A5:M5000") = ""

  • Assigning the Selection to a range and then selecting this range again and pasting it:

Dim myRange as Range
Set myRange = Selection
Worksheets("ZRFI08TW").Range("A5:M5000").ClearContents
myRange.Select
myRange.Copy
ActiveSheet.Range("A5").Select
SendKeys "^v"

But there should be a way better way to achieve what you are looking for. And as I am mentioning .Select, I feel an urge to mention this one - How to avoid using Select in Excel VBA.

Vityata
  • 42,633
  • 8
  • 55
  • 100