Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old August 30th 05, 07:07 PM
Dave Peterson
 
Posts: n/a
Default

I had a mistake in today's code. I pasted to the bottom of sheet2 column A.
But then I didn't go to the next cell.

Option Explicit
Sub testme2()
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("Sheet1")

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, "z").Value
'I added this to go down one row.
Set DestCell = Destcell.offset(1,0)
Next myCell
End With
End Sub

It does assume that the cell in column A of the input worksheet is not empty!



Optitron wrote:

I copied what you wrote and renamed sheet2 to DRMO SHEET but nothing
happened. I took what you already showed me yesterday and I can get one
row to the sheet with this:

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

Set actWks = ActiveSheet
Set prtWks = Worksheets("DRMO SHEET")

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

With prtWks
For Each myCell In myRng.Cells
iRow = myCell.Row
Range("a6").Value = actWks.Cells(iRow, "a").Value
Range("e6").Value = actWks.Cells(iRow, "z").Value
Application.Calculate
Application.Goto .Range("a1"), scroll:=True
Next myCell
End With
End Sub

Dave Peterson Wrote:
Maybe something like:

Option Explicit
Sub testme2()
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("Sheet1")

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, "z").Value
Next myCell
End With
End Sub

I'm guessing that z12 meant column Z.


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


--

Dave Peterson

  #22   Report Post  
Old August 30th 05, 08:56 PM
Optitron
 
Posts: n/a
Default


Ok it's working. It put the data on the next page down. It's all good
now. Thanks.

Dave Peterson Wrote:
I had a mistake in today's code. I pasted to the bottom of sheet2
column A.
But then I didn't go to the next cell.

Option Explicit
Sub testme2()
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("Sheet1")

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, "z").Value
'I added this to go down one row.
Set DestCell = Destcell.offset(1,0)
Next myCell
End With
End Sub

It does assume that the cell in column A of the input worksheet is not
empty!



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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copy 45 rows down, repetativly, only on button click? swatsp0p Excel Worksheet Functions 1 May 25th 05 04:06 PM
copy exact values from RangeA to Range B which has extra rows [email protected] .com Excel Discussion (Misc queries) 1 May 16th 05 09:21 AM
Copy Rows if Lookup Criteria Match vdoubleu Excel Discussion (Misc queries) 0 March 29th 05 06:43 PM
Copy rows of data to another worksheet where ReturnDate is blank Helen McClaine Excel Discussion (Misc queries) 2 March 28th 05 11:33 PM
How Can I copy a sheet that has hidden rows without copying the h. ibrahim Excel Worksheet Functions 1 January 13th 05 01:25 AM


All times are GMT +1. The time now is 02:54 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017