Method to copy and paste not working (VBA)

[iand5]

I have two different approaches to copy and paste data from one spreadsheet to another.

For some reason, the first approach which requires more lines of code works while the shorter version doesn't.

I've tried the shorter version for other cells and it works perfectly. Any ideas on why it doesn't work would be greatly appreciated.

1st approach:

Windows("Schedule.xlsx").Activate
    Sheets("QF.IPP").Select
    Range("P10,T10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("ID_DOG.xlsm").Activate
    Range("I30").Select
    ActiveSheet.Paste

2nd approach:

Workbooks("Schedule.xlsx").Sheets("QF.IPP").Range("P10,T10").Copy
    Workbooks("ID_DOG.xlsm").ActiveSheet.Range("I30").PasteSpecial

 

[Engineer Joe]

So, you're probably a new member. First things first. Make sure both of those workbooks are open. Go to the code for the second approach...i'm guessing it's in it's own subroutine...maybe it's all of the sub-routine. Either way, you need to get familiar with using Watches and stepping through code. You can use the errors generated to tell you what is probably wrong with the code. If it's part of a bigger subroutine, go to the part of the code shown in your second approach and hit "F9." That should create a breakpoint. That means anytime you execute a macro, it will pause at that line and wait for you to tell it to go forward. The way to step through code is to press F8. So, if you select on a subroutine or function and press F8, it will execute one line for every time you press F8. This allows you to see the line that is the problem. If you have to go through a bunch of other code to get to the line you care about, you just put your breakpoint (F9) at the place you care about seeing and then press F5 (which is the same as telling a routine to run.) VBA has to start at the beginning of a function or routine. You can't just select on a line and tell it to execute (unless you are using the "Immediate" window). Also, before you run any code, go ahead and click on the "Run" tab in the VBA editor and then click Reset. You can't run a subroutine right in the middle of other code executing, so you have to reset. So, use the F8 stepping function to see which of the lines causes the error. And you should also, when you ask the questions on here, say what error was generated. Saying something works or it doesn't is less information than what you could provide. BTW, I ran your 2nd approach on some sheets I made and it worked fine. You may want to check that your sheet names and workbook names are correct and that they are both open.

 

[MARK858]

It doesn't work because Workbooks("ID_DOG.xlsm") probably isn't the activeworkbook, Workbooks("Schedule.xlsx") probably is and so the activesheet is on Workbooks("Schedule.xlsx").

Use the actual sheetname then you can just do

Workbooks("Schedule.xlsx").Sheets("QF.IPP").Range("P10,T10").Copy Workbooks("ID_DOG.xlsm").Sheets("yoursheetname").Range("I30")

all on the one line.

 

[Engineer Joe]

MARK858,
I thought that too, but when I ran the code, I did it a number of ways. Both times that I executed, it didn't care about which workbook was active. And if you look at the help on "Activesheet", it appears that you don't have to use that on an Activeworkbook if you identify the workbook object before Activesheet, which user iand5 did. Although I do like your method better than what the poster used, I can't verify that the target paste workbook not being the active one is the actual issue. Activesheet doesn't have to apply to the activeworkbook. I'm using Excel 2016, so maybe earlier versions require that. I remember dealing with weird copy/paste shenanigans over the years.

 

[iand5]

Straight to the point. It worked.

Thanks