Using EXCEL add-in's worksheet for saving data.

[draretired]

I use the following code in an EXCEL add-in to swap rows:

For Col = col1 To col2
Cells(RW2, Col).Copy (ThisWorkbook.Sheets(1).Range
("A1"))
Cells(RW1, Col).Copy (Cells(RW2, Col))
ThisWorkbook.Sheets(1).Range("A1").Copy (Cells(RW1,
Col))
Next Col

It stopped working. Is there a problemm doing this?

Thanks,
Don

 

[jaf]

Hi Don,
Did you add or move some sheets?
..sheets(1) is the first (or left most) sheet as you see the sheet tabs.

--
John
johnf 202 at hotmail dot com


| I use the following code in an EXCEL add-in to swap rows:
|
| For Col = col1 To col2
| Cells(RW2, Col).Copy (ThisWorkbook.Sheets(1).Range
| ("A1"))
| Cells(RW1, Col).Copy (Cells(RW2, Col))
| ThisWorkbook.Sheets(1).Range("A1").Copy (Cells(RW1,
| Col))
| Next Col
|
| It stopped working. Is there a problemm doing this?
|
| Thanks,
| Don
|

 

[draretired]

No. I can't, because it's an add-in. I can't see the
worksheets.

Don

 

[Tushar Mehta]

Ummm...what does "It stopped working" mean? Compile error? Runtime
error? Something else?

Also, you might want to eliminate the loop. An untested alternative:
with activeworkbook.activesheet
..cells(rw2,col1).resize(1,col2-col1+1).copy _
ThisWorkbook.Sheets(1).Range("A1")
..cells(rw1,col1).resize(1,col2-col1+1).copy _
.cells(rw2,col1)
ThisWorkbook.Sheets(1).Range("A1").resize(1,col2-col1+1).copy _
.cells(rw1,col1)
end with

or, even better(?), use a VBA variant as in the tested:
Sub testIt()
Dim X
Const Col2 = 5, Col1 = 2, RW1 = 2, RW2 = 4
With ActiveWorkbook.ActiveSheet
X = .Cells(RW2, Col1).Resize(1, Col2 - Col1 + 1).Formula
.Cells(RW2, Col1).Resize(1, Col2 - Col1 + 1).Formula = _
.Cells(RW1, Col1).Formula
.Cells(RW1, Col1).Resize(1, Col2 - Col1 + 1).Formula = X
End With
End Sub
and, if you have only values and no formulas, replace the .Formula with
..Value

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[draretired]

Coooool!

Thanks,
Don

-----Original Message-----
Ummm...what does "It stopped working" mean? Compile error? Runtime
error? Something else?

Also, you might want to eliminate the loop. An untested alternative:
with activeworkbook.activesheet
..cells(rw2,col1).resize(1,col2-col1+1).copy _
ThisWorkbook.Sheets(1).Range("A1")
..cells(rw1,col1).resize(1,col2-col1+1).copy _
.cells(rw2,col1)
ThisWorkbook.Sheets(1).Range("A1").resize(1,col2- col1+1).copy _
.cells(rw1,col1)
end with

or, even better(?), use a VBA variant as in the tested:
Sub testIt()
Dim X
Const Col2 = 5, Col1 = 2, RW1 = 2, RW2 = 4
With ActiveWorkbook.ActiveSheet
X = .Cells(RW2, Col1).Resize(1, Col2 - Col1 + 1).Formula
.Cells(RW2, Col1).Resize(1, Col2 - Col1 + 1).Formula = _
.Cells(RW1, Col1).Formula
.Cells(RW1, Col1).Resize(1, Col2 - Col1 + 1).Formula = X
End With
End Sub
and, if you have only values and no formulas, replace the .Formula with
..Value

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions


.