Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Critique my filter/copy/visiblecells code...

This works pretty good, but seems to me to not be the "proper" use of .VisibleCells.

Where the code looks at all sheets in the array, column E, if the value in E has an "X" in the adjacent column D, then copy E value to sheet 1 column AG next open cell.

Is fairly fast, but only using 20 or so rows on each sheet.

Thanks,
Howard

Sub Many_To_One_Copy_1()

Dim i As Long
Dim MyArr As Variant
Dim lr As Long
Dim rngE As Range, c As Range

MyArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets(MyArr(i))

.AutoFilterMode = False

lr = .Cells(.Rows.Count, 5).End(xlUp).Row
Set rngE = .Range("$D$1:$E$" & lr)

rngE.AutoFilter Field:=1, Criteria1:="=X"
.Range("$E$2:$E$" & lr).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").Range("AG" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

rngE.AutoFilter

End With
Next 'i

Application.ScreenUpdating = True
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Critique my filter/copy/visiblecells code...

Just another way, but uses less code...

Sub Many_To_One_Copy_2()
Dim rng As Range, lLastRow&, v
Const sShtsList$ = "Sheet2,Sheet3,Sheet4,Sheet5"

Application.ScreenUpdating = False
For Each v In Split(sShtsList, ",")
With Sheets(v)
.AutoFilterMode = False
lLastRow = .Cells(.Rows.Count, 5).End(xlUp).Row
Set rng = .Range("$D$1:$E$" & lLastRow)

rng.AutoFilter Field:=1, Criteria1:="=X"
.Range("$E$2:$E$" &
lLastRow).SpecialCells(xlCellTypeVisible).Copy
Sheets("Sheet1").Range("AG" &
Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
rng.AutoFilter
End With 'Sheets(v)
Next 'v
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Critique my filter/copy/visiblecells code...

Hi Howard,

Am Tue, 7 Jun 2016 16:31:24 -0700 (PDT) schrieb L. Howard:

This works pretty good, but seems to me to not be the "proper" use of .VisibleCells.


do you have issues?
For me it works fine and with the cisible cells you are on the save
side.
But Copy method only copies visible cells and so SpecialCells are not
needed:
Sub Many_To_One_Copy_1()

Dim i As Long
Dim MyArr As Variant
Dim lr As Long
Dim rngE As Range, c As Range

MyArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets(MyArr(i))
.AutoFilterMode = False

lr = .Cells(.Rows.Count, 5).End(xlUp).Row
Set rngE = .Range("$D$1:$E$" & lr)

rngE.AutoFilter Field:=1, Criteria1:="=X"
.Range("$E$2:$E$" & lr).Copy
Sheets("Sheet1").Range("AG" & Rows.Count).End(xlUp)(2).PasteSpecial
Paste:=xlPasteValues

.AutoFilterMode = False

End With
Next 'i

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Critique my filter/copy/visiblecells code...

But Copy method only copies visible cells and so SpecialCells are not
needed:


Yes, indeed. It also just pastes values resulting from formulas. In
that case, if no formatting, PasteSpecial won't be necessary either.
(Just specify destination!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Critique my filter/copy/visiblecells code...

On Wednesday, June 8, 2016 at 12:37:42 AM UTC-7, GS wrote:
Just another way, but uses less code...


Hi Garry,

That's a pretty nifty way to get it done also. I like that.

Thanks.

Howard


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Critique my filter/copy/visiblecells code...



do you have issues?
For me it works fine and with the cisible cells you are on the save
side.
But Copy method only copies visible cells and so SpecialCells are not
needed:
Sub Many_To_One_Copy_1()

Dim i As Long
Dim MyArr As Variant
Dim lr As Long
Dim rngE As Range, c As Range

MyArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets(MyArr(i))
.AutoFilterMode = False

lr = .Cells(.Rows.Count, 5).End(xlUp).Row
Set rngE = .Range("$D$1:$E$" & lr)

rngE.AutoFilter Field:=1, Criteria1:="=X"
.Range("$E$2:$E$" & lr).Copy
Sheets("Sheet1").Range("AG" & Rows.Count).End(xlUp)(2).PasteSpecial
Paste:=xlPasteValues

.AutoFilterMode = False

End With
Next 'i

Application.ScreenUpdating = True
End Sub


Regards
Claus B.



Hi Claus,

No issues to speak of, was wondering if I was using VisibleCells correctly, or even if I should omit VisibleCells since I only wanted the E column.

One method I used VisiblCells and it copied Headers, column D and column E of filters data.

Thanks.

Howard

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
Critique a function to convert time, please Prof Wonmug Excel Programming 2 April 30th 10 04:43 PM
Copy only visible cells after filter is applied/ sum after filter MAM Excel Worksheet Functions 0 April 9th 08 04:09 AM
critique my project Jeff Higgins Excel Programming 2 February 10th 05 02:17 PM
Efficiency in my code (a critique from the guru's) Wally Steadman[_4_] Excel Programming 1 December 30th 04 01:45 PM
The For Loop Worked, but need critique Bruce Roberson Excel Programming 8 August 2nd 03 09:17 AM


All times are GMT +1. The time now is 10:37 PM.

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

About Us

"It's about Microsoft Excel"