Pasting in incorrect sheet

[Yorke]

Hi all,

I have some code which copies and pastes data from one workbook into another. It pulls data from the correct workbook, and then pastes into the other workbook (this is good). However, instead of pasting into column 'S' in the "data" sheet, it pastes into column 'S' in the "Status" sheet. Please see the code below:

Set OpenBook = Workbooks.Open("C:\Users\yorke\OOH.xlsx")

OpenBook.Sheets("OOH Data").Range("ZVA05Data[[#All],[Item No]]").Copy 'Copy Item No column from table
ThisWorkbook.Worksheets("Data").Range("S1").PasteSpecial xlPasteValues 'Paste values into Data tab in S1

As you can see, I don't even mention the "status" sheet, however there are some lines of code referring to the "status" sheet earlier in the code. Is there anyway I can make this more specific? It began not working with this and now another column is also pasting in the incorrect sheet.

Thanks in advance.

 

[6StringJazzer]

Sometimes a problem is not where you think it is. This code looks OK but please show us all the code for the entire Sub. For example, there could be something happening after this code that causes the problem. Have you stepped through the code with F8 to see what happens on that exact line of code with the Paste?

Also, I suggest applying code tags when you paste code into post. Select the code then click the VBA icon in the editor. This will preserve the spacing of your code and color-code keywords.