Help with copying multiple rows ??

sp4rky

New Member
Joined
Nov 15, 2009
Messages
1
First of all the following code was thanks to this great site. It works a treat however i have a very small query

Sub Transport()
Dim RngColF As Range
Dim i As Range
Dim Dest As Range
Sheets("All Members").Select
Set RngColF = Range("W1", Range("W" & Rows.Count).End(xlUp))
With Sheets("Transport Volunteer")
Set Dest = .Range("A1")
End With
For Each i In RngColF
If i.Value = "X" Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
End Sub


This will copy the exact Row to a new sheet that matches the format in column W. As i said works fine however i do not want all the row information copied only certain columns in the matching rows. I dont know if there is a range command that i could use instead of EntireRow. Can any one help. Then the next thing i wondered is can i apply this to a number of columns simultaneously?
And finally is it possible to creat buttons that will run the macro rather having to get some one who is not as puter savvy to try and run code?

be grateful for your feedback,

Carl.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
try these codes
Code:
Sub Carl()
Dim a As Long, d As Long
d = 2
    For a = 2 To Sheets("All Members").Range("W65536").End(xlUp).Row
        If Sheets("All Members").Cells(a, 1) = "X" Then
        Sheets("All Members").Rane("A" & a & ":Z" & a).Copy
        Sheets("Transport Volunteer").Range("A" & d).PasteSpecial
        d = d + 1
        End If
    Next a
MsgBox "Complete"
End Sub
you can run it on multiple columns and specify how your output should be.Codes need to be tailored to your requirement.Draw any object on the worksheet, right click on it and choose "Assign macro" . Next time you click on it, it will run the macro.
ravi
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,416
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top