Copying data from

[r.baldwin7960]

Thank you in advance for any help I might recieve.

Sub DataCopy()
 
    Application.ScreenUpdating = False
 
    Sheets("Sort Sheet").Select
 
    Columns("C:CI").Select
    Selection.ClearContents
 
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "='Query Page'!R[6]C[-2]"
 
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C312"), Type:=xlFillDefault
 
    Range("C1:C312").Select
    Selection.AutoFill Destination:=Range("C1:CI312"), Type:=xlFillDefault
 
    Sheets("Main Sheet").Select
 
    Application.ScreenUpdating = True
 
End Sub

I have a web query that pulls a table of sales associates and their performance metrics. The table only contains the associates whom have metrics from the previous day. So the list is not always the same. I then need to copy that data to the "Sort Page" sheet. This way I can sort the associates out against a master list in order to use the VLOOKUP to create reports on the "Main" sheet.

My problem is this:

Because the number of associates changes on the table, I am constantly having to modify the macro to copy only the cells needed. (Otherwise the Sorting macro I use does not work properly)

What I need is a way to only copy data from the "Query" page from the starting point to the row right above the cell containing "Grand Totals". The columns are usually static, only the rows change.

 

[Datsmart]

I would suggest looking at Advanced Filtering to do what you are after.
Good example of Advanced Filter steps here:
http://www.contextures.com/xladvfilter01.html

Another good link here:
http://www.meadinkent.co.uk/xlfilter.htm

Advanced Filter allows you to preset filter options.
It also allows you to filter by criteria in ways standard autofilter can not do. You can choose an export location in the dialog box or in VBA code.
Once you setup the code and criteria, you can use a macro button to copy specific data from your main sheet to a "Report" sheet.

 

[r.baldwin7960]

I think this might be able to help me, but I'm not sure. I'll look into it further to see what I can figure out, thank you for the tip!

Just to try to clarify a little better, the "query" sheet pulls multiple sets of data onto one sheet. The table data I need to copy to my "sort" sheet starts in cell A7, the number of rows and columns varies according to the day. (typically around 300 rows, and 80 columns). There is no data in rows below the table, or in columns to the right of the table.

Question: With Advanced filter, can I select a dynamic range with the upperleft most cell as A7 with rows extending down to 2nd to the last non-empty row (the last of which always displays "Totals" in column A of that particular row), and extending the columns to include all columns to the next empty column in row 7?

 

[Datsmart]

Take a look at the suggested code in the following link:
http://www.mrexcel.com/forum/showthread.php?p=1157639

In that code "CurrentRegion" is used.

Sheets("Sheet1").Range("A7").CurrentRegion.Name = "CurrentData"

This code assigns the RangeName of "CurrentData" to all cells congruent to A7.
There are other methods to determine the last row of data if this doesn't work for you.

 

[r.baldwin7960]

After looking at this some more, it seems like this is more complex than what I need. Not that it is too hard, just that it seems like I'm taking a lot of extra steps.

when you say:

"This code assigns the RangeName of "CurrentData" to all cells congruent to A7."

...does this mean that I can say that the table starts in cell A7, and it will automatically copy the whole table?

I don't need to filter it in any way. I just need to copy the entire table. I've even decided that I can deal with it copying the last row of data as well.

I apologize for not quite understanding it properly, I'm still fairly new at some of the advanced excell capabilities, though I will say I'm learning quite fast. I just wished I knew VB a little bit better than I do. If there is anymore info that I can give, please let me know. I would have sent the document itself if I didn't have to worry about confidentiality through work...

 

[Datsmart]

You are correct. The code will assign a RangeName to all cells Adjacent, (connected) to cell A7. Anything separated by one row or column will not be included.
You can test what it will use by selecting cell A7, then hitting Ctrl-Shift-8.
This will select all cells Congruent to A7.