Excel VBA Search/Select

[acford]

Hi all,

Using the following code to scan through a spreadsheet to select specific values and then copy a range of data to a blank sheet. I have about 6 or 7 chunks of this code in one module for different values. Is there an easier way to do this? Also if the value does not exist to run bombs out when it reaches the bottom of the spreadsheet.

-------------------------------------

Range("H2&quot.Select
Do Until ActiveCell.Value = 45860
movedown
Loop
Selection.End(xlToLeft).Select
ActiveCell.Name = "StartCell"
For i = 1 To 7
moveright
Next i
Do While ActiveCell.Value = 45860
movedown
Loop
moveright
moveup
ActiveCell.Name = "EndCell"
Range("StartCell", "EndCell&quot.Select
Selection.Copy
Range("A2&quot.Select
Windows("blank.XLS&quot.Activate
ActiveSheet.Paste
Do Until ActiveCell.Value = ""
movedown
Loop
Windows("source.xls&quot.Activate

------------------------------------

Any help would be great...

 

[Zathras]

I would suggest you exploit the functionality of Auto Data Filter (Data/Filter/AutoFilter).

Record a new macro to capture the steps of:
1. Establish an AutoFilter.
2. Select the value you want from the column you want.
3. Copy the range that is showing. (A1:G10000)
4. Paste the results in your blank.xls spreadsheet.
5. Turn off the AutoFilter.

 

[acford]

That's good but it all has to be automated, altogether there are 6 modules that have to run with no user intervention. The way I have it at the moment works. But it is a lot to maintain.

 

[xlbo]

acford - Zathras was explaining how to record yourself performing the actions manually so that they can be translated to code (and therefore be automated)

Use Tools>MAcros>Record new macros to kick off the process

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244