Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create seperate lists
I have been trying to piece together what I'm after but don't seem to be able
to do it. I have a sheet named "Monthly". What I would like to do is loop through the rows in "Monthly" find where the date value in column K matches the value typed into cell A1, copy that row and paste into another sheet in the workbook (already created) called "Sub Lists", starting the pasting from cell G4. If it weren't getting too difficult, is it possible instead of copying the entire row that matches the criteria in column K to only copy certain cells in that row. For example, if K6 had a date value that match the date typed into cell A1, instead of copying the entire row 6, only the values in A6,C6,D6,G6,K6,I6 would be copied and pasted into the other sheet (still starting in cell G4). I hope I explained that ok, any help would be much appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create seperate lists
Hi
Try this: Sub copy() Dim TargetRange As String Dim CopyRow As Range Application.ScreenUpdating = False TargetRange = "A1:Q1000" Worksheets("Monthly").Activate Range(TargetRange).Select Selection.AutoFilter FIeld:=11, Criteria1:=Range("A1").Value Set HelpSH = Worksheets.Add Worksheets("Monthly").Activate Selection.copy HelpSH.Range("A1") HelpSH.Activate Range("K2", Range("k2").End(xlDown)).Select For Each r In Selection.Rows Set CopyRow = Application.Union(Cells(r.Row, 1), Cells(r.Row, 3), _ Cells(r.Row, 4), Cells(r.Row, 4), Cells(r.Row, 7), _ Cells(r.Row, 11), Cells(r.Row, 9)) CopyRow.copy Destination:=Worksheets("Sub Lists").Cells(4 + off, "G") off = off + 1 Next With Application .CutCopyMode = False Selection.AutoFilter .DisplayAlerts = False HelpSH.Delete .DisplayAlerts = True .ScreenUpdating = True End With End Sub Best regards, Per "Tony" skrev i meddelelsen ... I have been trying to piece together what I'm after but don't seem to be able to do it. I have a sheet named "Monthly". What I would like to do is loop through the rows in "Monthly" find where the date value in column K matches the value typed into cell A1, copy that row and paste into another sheet in the workbook (already created) called "Sub Lists", starting the pasting from cell G4. If it weren't getting too difficult, is it possible instead of copying the entire row that matches the criteria in column K to only copy certain cells in that row. For example, if K6 had a date value that match the date typed into cell A1, instead of copying the entire row 6, only the values in A6,C6,D6,G6,K6,I6 would be copied and pasted into the other sheet (still starting in cell G4). I hope I explained that ok, any help would be much appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to create seperate sheets | Excel Programming | |||
validation lists from seperate work books | Excel Discussion (Misc queries) | |||
validation lists from seperate work books | Excel Discussion (Misc queries) | |||
macro to create lists | New Users to Excel | |||
reconising matching no's in 2 seperate lists | Excel Programming |