load output to a new worksheet

[wafs]

In the code I'm looking for part numbers that have not been shipped in over 30 days. I have my if statement set up, but I need for it to look at the over 30 days and move the part number, which is in another column.

If Sheets(sh).Range(TestRange) > 30 Then
TestRange = "AF" + i
TestRange2 = "AE" + i
parts = parts + 1
'Parts number

Output(1) = "A" + i 'does not look to see if AF is greater then 30
Output(2) = Sheets(sh).Range(Output(1)).Value

'Output info to Over 30 sheet
m = parts + 2
reportlocation = "A" + m
Sheets(destsh).Range(reportlocation).Value = Output(2)
'n = n + 1
TestRange = "AF" + i
TestRange2 = "AE" + i
i = i + 1
End If

All the help in the world would be greatly appreciated.

Wendy

 

[SkipVought]

Hi,

It would be alot simpler to use MS Query via Data/Get External Data/New Database Query -- Excel Files -- YOUR WORKBOOK.......

I do this sort of thing VERY OFTEN. The QBE Editor makes it a GUI point 'n' click exersize in most cases.

Using MS Query to get data from Excel faq68-5829



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[wafs]

It is not external data, it is data another worksheet. It is all in excel and nothing in database.

 

[SkipVought]

Did you read the FAQ?

Is your sheet NOT configured as a LIST or TABLE -- it most likely ought to be.

As a LIST or TABLE, it can be accessed as a DATABASE. Simplifies LOTS of issues!

Guidelines for creating a list on a worksheet
Microsoft Excel has a number of features that make it easy to manage and analyze data in a list. To take advantage of these features, enter data in a list according to the following guidelines.

List organization
Use only one list per worksheet Avoid having more than one list on a worksheet. Some list management features, such as filtering, can be used on only one list at a time.

Put similar items in one column Design the list so that all rows have similar items in the same column.

Keep the list separate Leave at least one blank column and one blank row between the list and other data on the worksheet. Excel can then more easily detect and select the list when you sort, filter, or insert automatic subtotals.

Position critical data above or below the list Avoid placing critical data to the left or right of the list; the data might be hidden when you filter the list.

Show rows and columns Make sure any hidden rows or columns are displayed before making changes to the list. When rows and columns in a list are not showing, data can be deleted inadvertently.

List format
Use formatted column labels Create column labels in the first row of the list. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the list. Format the cells as text before you type the column labels.

Use cell borders When you want to separate labels from data, use cell borders — not blank rows or dashed lines — to insert lines below the labels. How to apply borders to cells.

Avoid blank rows and columns Avoid putting blank rows and columns in the list so that Excel can more easily detect and select the list.

Don't type leading or trailing spaces Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell.

Extend list formats and formulas When you add new rows of data to the end of a list, Excel uses consistent formatting and formulas. Three of the five preceding cells must use the same format or formula for this to occur. How to turn on extended list formats and functions.

More information
Use a list as a database

Add to or change a list by using a data form

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}