-1

On one tab, I have a list of depots that all have a unique depot number attributed to them. On the other tab, I have a list of data that includes numerous lines for each depot with their depot numbers on each line.

I basically need to create a file for each depot with only that depots info in it.

Usually, I would start at the top of the list of depots, and use 

While Not ActiveCell.Value=""

Then I copy the sheet, delete any lines that didnt involve that depot, save as a new file and then use ActiveCell(2,1).Select to select the next depot and repeat.

However, I need a way of doing this without using select. Can someone advise please?

Tom Beasley
  • 11
  • 2
  • 1
    Yes I have seen that but |can't seem to find what I need for my specific scenario – Tom Beasley Jul 29 '16 at 14:42
  • 1
    Read through [how to avoid using `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – BruceWayne Jul 29 '16 at 14:50
  • @TomBeasley - I suggest incorporating the *principles* in the avoiding select answer into your code and reposting a more specific question with any issues you have. We'll be able to help more precisely that way. – Scott Holtzman Jul 29 '16 at 14:56
  • 1
    Write a code with `.Select` first. Once you have a working code, paste that code here and then we will recommend how to remove those `Selects` ;) – Siddharth Rout Jul 29 '16 at 14:57

1 Answers1

0

Add a variable to hold a row number and wrap your code in a loop, that should do it.

Public Sub Sample()
Dim LngRow as Long
Dim WkSht as Worksheet

Set WkSht = ThisWorkbook.Worksheets("Sheet1")
    LngRow = 2
    Do Until WkSht.Range("A" & LngRow) = ""
        ExportDepot WkSht.Range("A" & LngRow)
        LngRow = LngRow + 1
    Loop
Set WkSht = Nothing
Gary Evans
  • 1,850
  • 4
  • 15
  • 30
  • There are a number of ways you could do this. I would suggest maybe sending your code to [Code Review](http://codereview.stackexchange.com/) to have it looked over. – Gary Evans Jul 29 '16 at 15:12