PDA

View Full Version : Solved: Copy Filtered list from Sheet 1 to Sheet 2



YellowLabPro
08-25-2007, 09:37 AM
Code posted for example: The loop is not really feasible, but is my first attempt to get my idea out and show how I might copy the data from the source to the target. The code is presented this way as a start to forming the logic.


Sub EditRecords()
Dim Wss As Worksheet, Wst As Worksheet
Dim i As Long, lLrws As Long
Set Wss = Workbooks("MasterImportSheetWebStore.xls").Sheets("TGFF")
Set Wst = Workbooks("MaintImport.xls").Sheets("Record Creator")
lLrws = Wss.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lLrws
Wst.Cells(i, "Y").Value = Wss(i, "D").Value
Next i

End Sub


I want to copy particular columns from sheet 1 to sheet 2.
Sheet 1 is filtered to show only the desired records.
There will always be a header, so row 1 is not to be included.
The destination row will always begin in row 6.

I was thinking of using something like the Visible Property, where it will perform the instructions on what is visible. Is this a permittable approach. It is a property so I am not clear if this is possible.

Bob Phillips
08-25-2007, 10:23 AM
Normally when copying filtered data you use SpecialCells(xlCellTypeVisible).

Are you wanting to copy the row or just one column?

YellowLabPro
08-25-2007, 10:28 AM
I want to copy the column

Bob Phillips
08-25-2007, 10:45 AM
Here's a starter



Sub EditRecords()
Dim Wss As Worksheet, Wst As Worksheet
Dim i As Long, lLrws As Long
Set Wss = Workbooks("MasterImportSheetWebStore.xls").Sheets("TGFF")
Set Wst = Workbooks("MaintImport.xls").Sheets("Record Creator")
lLrws = Wss.Cells(Rows.Count, "A").End(xlUp).Row
Wss.Range("D2").Resize(lLrws - 1).SpecialCells(xlCellTypeVisible).Copy Wst.Cells(6, "Y")
End Sub

YellowLabPro
08-25-2007, 10:48 AM
thanks Bob,
I will start off w/ this and post back

Norie
08-25-2007, 11:32 AM
Try advanced filter.

YellowLabPro
08-25-2007, 12:50 PM
Bob-
Not much of a starter, rather.............
Right out of the box- HomeRun Baby!

Thanks again....

YellowLabPro
08-25-2007, 12:51 PM
Bob- Got something for you.... & anyone else who likes good music whilst tapping away

http://www.npr.org/templates/story/story.php?storyId=13889716