Error 1004 on Copying and Pasting Data

Question

I get an error message, Run-time error 1004 (Application-defined or object defined error).

Basically, at Column #30 (whatever letter(s) that corresponds to), it is supposed to copy and paste a certain selection. Next, depending on the number of bidders there are, let's say 2, every next 8 columns there will be another selection it needs to copy and paste to the new worksheet.

So say I have 2 bidders:

Column #30 and Column #38 would then need to be copy and pasted to the other worksheet side-by-side. Can anyone help me out here? I have no problem with the input popping up, but once it reaches this part to start copy and pasting the columns it gives an error messsage.

''''Get adsad
For i = 0 To bidNum - 1
Range(colRange.Offset(0, 30 + i * 8)).Select
Range(colRange.Offset(0, 30 + i * 8)).EntireColumn.Copy
Worksheets("Per FOB Delivered").Activate
colRange.Offset(0, 11 + i).Select
colRange.Offset(0, 11 + i).Paste
With Selection
.HorizontalAlignment = xlGeneral
.Font.Name = "Calibri"
.Font.Size = 10
.EntireColumn.AutoFit
End With
Next i
ActiveCell.Offset(0, -10).Range("A1").Select
Sheets("Master Calc").Select

the declarations are here:

'Declarations
Dim bidNum As Integer
Dim colRange As Range
Set colRange = Range("A1")

'User Input
bidNum = InputBox("Please Enter the number of bidders: ")

Edit: I believe I have narrowed the problem down to this: Worksheets("Per FOB Delivered").Activate I set everything to comment and the code worked, and then I went line by line and it stopped working when I reached this part of the code (prompts that error message). I tried:

ActiveWorkbook.Sheets("Per FOB Delivered").Activate

and it didn't work either. For the record, my code does create that page and works up until this portion (didn't list because it's kind of long).

Here's a snippet of some of the other code that works perfectly okay, note, this section is before the other one and there are other similar portions like this. I have no errors with this code listed:

'Get Current Net FOB Overage /lb
Range("P1").Select
Range("P1").EntireColumn.Copy
Sheets("Per FOB Delivered").Activate
ActiveCell.Range("J1").Select
ActiveSheet.Paste
    With Selection
        .HorizontalAlignment = xlGeneral
        .Font.Name = "Calibri"
        .Font.Size = 10
        .EntireColumn.AutoFit
    End With
ActiveCell.Offset(0, -9).Range("A1").Select
Sheets("Master Calc").Select

In general it is bad practice to be selecting cells within VBA. Please read [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Also, are sure that you have a sheet named `Per FOB Delivered` ? — chancea, Jan 27 '15 at 16:46
Yep. Per FOB Delivered 100% exists and gets the other portions of my code to work. I added in a sample of the other code that works and pasts to the "Per Fob Delivered" page. — Michael, Jan 27 '15 at 16:49
Are you unable to debug this in the VB editor window? If you can run your code directly in the editor window it will highlight which line is causing the error. — chancea, Jan 27 '15 at 16:55
Nevermind, still stuck. colRange.Offset(0, 10 + i).Paste This is causing problems somehow. Probably due to the merged column it copies. Basically, row 3, Column #__ has a title in row 1 that is merged with other columns for row 1. — Michael, Jan 27 '15 at 17:18

score 0 · Answer 1 · answered Jan 27 '15 at 18:16

It's already been mentioned in the comments, but you should avoid using .Select as much as possible. Often times it's unnecessary and your code will run faster and look a lot simpler if you avoid it.

As to your issue, use

colRange.Offset(0, 11 + i).PasteSpecial

instead of .Paste, merged cells shouldn't make a difference.

Error 1004 on Copying and Pasting Data

1 Answers1