ActiveSheet.Paste Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm having a problem running a macro in Excel. This macro takes 4 other worksheets, changes some columns and then pastes all the remaining data into one worksheet. On the last worksheet, I keep on getting the following Visual Basic RunTime error (which is because of the ActiveSheet.Paste code)

Run-time error '1004'

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following

-Click a single cell, and then paste
-Select a rectangle that's the same size and shape, and then paste

*************************************
Any ideas of what I can do to solve this problem

Thanks in advance.
 
Show us your code

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Matt said:
I'm having a problem running a macro in Excel. This macro takes 4 other worksheets, changes some columns and then pastes all
Click to expand...
the remaining data into one worksheet. On the last worksheet, I keep on getting the following Visual Basic RunTime error (which
is because of the ActiveSheet.Paste code):
 
Are they any merged cells on the spreadsheet you are trying to copy? Merged cell do not copy through VBA.
To correct the problem is to remove Merged Cells and replace them with Format|Format Cells|Alignment\Horizontal "Center Across Selection"


----- Matt wrote: ----

I'm having a problem running a macro in Excel. This macro takes 4 other worksheets, changes some columns and then pastes all the remaining data into one worksheet. On the last worksheet, I keep on getting the following Visual Basic RunTime error (which is because of the ActiveSheet.Paste code)

Run-time error '1004'

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following

-Click a single cell, and then paste
-Select a rectangle that's the same size and shape, and then paste

*************************************
Any ideas of what I can do to solve this problem

Thanks in advance.
 
Here is my code

Private Sub AddIMToConsolidated(

Sheets("Sheet1").Selec
Range("A3").Selec
Selection.End(xlDown).Selec
ActiveCell.Offset(0, 19).Selec
ActiveCell.Selec
Range(Selection, "A1").Selec
Selection.Cop
Sheets("Consolidated").Selec
Range("A3").Selec
Selection.End(xlDown).Selec
ActiveCell.Offset(1, 0).Selec
ActiveSheet.Paste ** This is where I have the error... the macro stops right here. *
Range("A1").Selec
Sheets("MainMenu").Selec
End Sub
 
If I understand you correct try this one without selecting

Private Sub AddIMToConsolidated()
Dim Rnum As Long
Dim Rnum2 As Long
With Sheets("Sheet1")
Rnum = .Range("A3").End(xlDown).Row
Rnum2 = Sheets("Consolidated").Range("A3").End(xlDown).Row
.Range("A3:T" & Rnum).Copy _
Sheets("Consolidated").Range("A" & Rnum2 + 1)
End With
Sheets("MainMenu").Select
End Sub
 
Hi Ron

I tried executing the code you gave me, but I still get the error message

The only other way for me to copy and paste the data is to copy the data into notepad and then paste it manually. For some reason, it works with my 3 other separate worksheets, but the last one keeps on giving me errors

Thanks for your help though

Regards
Matt
 
I have encountered this same error in copy|paste coding. One works and the other errors out. The problem I had was not in my coding, but in my formatting. One of the spreadsheets had merged cells and VBA seems to have a problem running insert or delete column in a range with merged cells. I have also found that the copy|paste function errors out in VBA with merged cells.

Possible solution: is to check the formatting on both sheets and removed all merged cells.
 
One of the spreadsheets had merged cells
Click to expand...

Don't use merged cells
Only trouble

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Foxman said:
I have encountered this same error in copy|paste coding. One works and the other errors out. The problem I had was not in my
Click to expand...
coding, but in my formatting. One of the spreadsheets had merged cells and VBA seems to have a problem running insert or delete
column in a range with merged cells. I have also found that the copy|paste function errors out in VBA with merged cells.
 
Back
Top