I got this macro from someone in this forum. It moves two specific cell
from one row on a sheet to two cells in a row on another sheet "DRMO".
tried to modify it to move three cells but if I select more than one ro
and click the button it fills them in horizontally instead o
vertically. For this one I need; cell A to cell A, cell BW to cell E
and cell AA to cell I.


Option Explicit
Sub DRMO()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("DRMO")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "BW").Value
Set DestCell = DestCell.Offset(0, 1)
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "AA").Value
Set DestCell = DestCell.Offset(0, 1)
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Su

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=47551

This portion

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "BW").Value
Set DestCell = DestCell.Offset(0, 1)
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "AA").Value
Set DestCell = DestCell.Offset(0, 1)
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With

should look more like:

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 4).Value = actWks.Cells(iRow, "BW").Value
DestCell.Offset(0, 8).Value = actWks.Cells(iRow, "AA").Value
Set DestCell = DestCell.Offset(0, 1)
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With

This line essentially moves down one row:
Set DestCell = DestCell.Offset(0, 1)

So you only have to do that when you're done plopping in the values for that
row.

And the .offset() lines like:
destcell.offset(x,y).value = ....

Destcell in in column A. .offset(x,y) says to "move" to x rows (up or down) and
y columns (right or left).

range("z99").offset(-1,-2) would "move" one row up and two columns to the left.

So DestCell.Offset(0, 8).Value = actWks.Cells(iRow, "AA").Value
moves to the same row (0 rows) and 8 columns to the right.



Optitron wrote:

I got this macro from someone in this forum. It moves two specific cells
from one row on a sheet to two cells in a row on another sheet "DRMO". I
tried to modify it to move three cells but if I select more than one row
and click the button it fills them in horizontally instead of
vertically. For this one I need; cell A to cell A, cell BW to cell E,
and cell AA to cell I.

Option Explicit
Sub DRMO()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("DRMO")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "BW").Value
Set DestCell = DestCell.Offset(0, 1)
DestCell.Offset(0, 1).Value = actWks.Cells(iRow, "AA").Value
Set DestCell = DestCell.Offset(0, 1)
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub

--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=475519

--

Dave Peterson

With what you gave me it moves column A to column A, and column AA to I
but not BW to E. Also when I try to select more than one row it take
one row and then the next row goes on the next row but over a fe
cells.
Is there maybe a simpler way? I just want to select a few rows, clic
a button and have certain cells in that row moved to the other sheet

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=47551

Dave copied the typo from your original code that offsets to the right
rather than down. Try:

Sub DRMO()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("DRMO")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 4).Value = actWks.Cells(iRow, "BW").Value
DestCell.Offset(0, 8).Value = actWks.Cells(iRow, "AA").Value
Set DestCell = DestCell.Offset(1, 0) '<<<changed
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub

Hope this helps
Rowan

Optitron wrote:
With what you gave me it moves column A to column A, and column AA to I,
but not BW to E. Also when I try to select more than one row it takes
one row and then the next row goes on the next row but over a few
cells.
Is there maybe a simpler way? I just want to select a few rows, click
a button and have certain cells in that row moved to the other sheet.

Good eyes!

Thanks for the correction.

Rowan Drummond wrote:

Dave copied the typo from your original code that offsets to the right
rather than down. Try:

Sub DRMO()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("DRMO")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 4).Value = actWks.Cells(iRow, "BW").Value
DestCell.Offset(0, 8).Value = actWks.Cells(iRow, "AA").Value
Set DestCell = DestCell.Offset(1, 0) '<<<changed
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub

Hope this helps
Rowan

Optitron wrote:
With what you gave me it moves column A to column A, and column AA to I,
but not BW to E. Also when I try to select more than one row it takes
one row and then the next row goes on the next row but over a few
cells.
Is there maybe a simpler way? I just want to select a few rows, click
a button and have certain cells in that row moved to the other sheet.



--

Dave Peterson

Will that code help me with this?

Move the selected row: column B, C, E, F, M, N - from sheet "NSN LIST
REF"

to column A, B, D, E, F, G - sheet "3 BIN".

Starting at row 1 and each active row moved needs to go 5 rows apart
(rows 1, 6, 11, 16, 21, etc...)


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=475519

Will that code help me with this?

Move the selected row: column B, C, E, F, M, N - from sheet "NSN LIST
REF"

to column A, B, D, E, F, G - sheet "3 BIN".

Starting at row 1 and each active row moved needs to go 5 rows apart
(rows 1, 6, 11, 16, 21, etc...)

Dave Peterson Wrote:
Good eyes!

Thanks for the correction.

Rowan Drummond wrote:

Dave copied the typo from your original code that offsets to the
right
rather than down. Try:

Sub DRMO()
Dim toWks As Worksheet
Dim actWks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim iRow As Long
Dim DestCell As Range

Set actWks = ActiveSheet
Set toWks = Worksheets("DRMO")

Set myRng = Intersect(Selection.EntireRow, actWks.Range("a:a"))

With toWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1,
0)
End With

With toWks
For Each myCell In myRng.Cells
iRow = myCell.Row
DestCell.Value = actWks.Cells(iRow, "a").Value
DestCell.Offset(0, 4).Value = actWks.Cells(iRow,
"BW").Value
DestCell.Offset(0, 8).Value = actWks.Cells(iRow,
"AA").Value
Set DestCell = DestCell.Offset(1, 0) '<<<changed
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub

Hope this helps
Rowan

Optitron wrote:
With what you gave me it moves column A to column A, and column AA
to I,
but not BW to E. Also when I try to select more than one row it
takes
one row and then the next row goes on the next row but over a few
cells.
Is there maybe a simpler way? I just want to select a few rows,
click
a button and have certain cells in that row moved to the other
sheet.



--

Dave Peterson


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=475519