Excel VBA coding problem
 

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.

Excel VBA coding problem
 

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.

"adubbs99" wrote:

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.

Excel VBA coding problem
 

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 ...
-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"adubbs99" wrote:

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.