Excel VBA coding problem

  • Thread starter Thread starter adubbs99
  • Start date Start date
A

adubbs99

I need to solve a macro problem ASAP. It involves problems with Excel VBA
skipping over the whole rest of a Subroutine right when I try to paste a
range (the copy/cut works fine, I believe). Code examples are below.

Here's an excerpt where the problem occurs:
With Workbooks("Benefits.xlsm").Worksheets(strMainSheet)
intLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
intLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
..Range(.Cells(1, 1), .Cells(intLastRow, intLastCol)).Cut
..Range(.Cells(1, 2), .Cells(intLastRow, intLastCol + 1)).PasteSpecial 'I've
tried .Paste as well
End With

BUT the following which occurs later in the routine works fine:
With Workbooks("Benefits.xlsm").Worksheets(strMainSheet)
intMainLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
..Range(.Cells(intMainLastRow + 2, 1), .Cells(intMainLastRow + 2 +
intLastRow, intLastCol)).PasteSpecial
End With

When I try to isolate it into its own Macro, I get various errors such as
"object doesn't support property/method", yet this doesn't occur when I run
it with the rest of the code.
 
You may be having problems with merged cells either in the source of
destination cells. A non-merged cells cannot be pasted on a meged cel.

check if you can perfrom the operation on the worksheet manually. The error
message will be better on the worksheet.
 
Try
Sub t()
With Workbooks("Benefits.xlsm").Worksheets(strMainSheet)
intLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
intLastCol = .Cells.SpecialCells(xlCellTypeLastCell).Column
..Range(.Cells(1, 1), .Cells(intLastRow, intLastCol)).Cut _
Destination:=.Range(.Cells(1, 2), .Cells(intLastRow, intLastCol + 1))
End With
End Sub

also to be on the safe side declare intLastRow (assuming you have declared
them as Integer, going by the name) etc as Long due ...
 
Back
Top