Only Choose desired Rows and Columns

[jeffwest1]

I have the below spreadsheet:

Advisor Type Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
A Barrett NEET 25 25 25 25 25 25 25 25 25 25 25 25
A Barrett In Learning 15 15 15 15 15 15 15 15 15 15 15 15
A Barrett Not Known 26 26 26 26 26 26 26 26 26 26 26 26
A Barrett YP JWT 29 29 29 29 29 29 29 29 29 29 29 29
A Barrett Teen Mums 1 1 1 1 1 1 1 1 1 1 1 1
A Barrett LDD EET 0 0 0 0 0 0 0 0 0 0 0 0
A Barrett CL 0 0 0 0 0 0 0 0 0 0 0 0
A Barrett Interventions 101 101 101 101 101 101 101 101 101 101 101 101
B Bunce NEET 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce In Learning 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce Not Known 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce YP JWT 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce Teen Mums 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce LDD EET 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce CL 2 2 2 2 2 2 2 2 2 2 2 2
B Bunce Interventions 423 423 423 423 423 423 423 423 423 423 423 423

I can already copy and paste the values from this spreadsheet into another, by searching for the Advisor A Barrat, from there i have hard coded choose the fields A1:N9 which give me all the months data for that person.

How can i code this to choose the data without hardcoding in the range.

I.e B Bunce's data is in A8:N17, but this may not always be the case, so hard coding it will not work as this list is going to be approx 200 people long, by 9 targets, with people joining and leaving all the time, however, it will always be the same number of months, so the code needs to look for B.Bunce (or who ever), then select all the data 14 columns across and 9/8 rows down.

I have tried offset, but this just chooses the last cell, any help would be appreciated.


--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[jeffwest1]

Sorry, just to clarify this, this is the code i am currently using

Sub FindId()

Dim Var1 As String
Dim CopyRange As Range

Var1 = 0
Var1 = Cells(4, 2) 'Find Advisor Name

Workbooks.Open Filename:="c:\Scorecardtest\Neet.xls" 'Open Consolidated workbook

Range("A1:G16").find(What:=Var1, LookAt:=xlWhole, _
LookIn:=xlValues, SearchOrder:=xlByColumns).Activate 'Look in spreadsheet

Set CopyRange = Range("A1:N9") 'Copy Data


CopyRange.Copy Destination:=Worksheets("Summary").Range("A1") 'Paste Range to spreadsheet

Workbooks("Neet.xls").Close 'Close Consolidated Workbook

End Sub

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[PHV]

Why not using an AutoFilter ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jeffwest1]

This needs to be an automated process.

The data will be coming out of a database via a crystal report overnight.

The VB code will hopefully run each time the speadsheet is opned to update the data, as i said, this will be approx 200 speadsheets needing to be updated with data everymonth.

Can you run auto filet as a VB process to filter, select copy and paste the data?

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

Jeff,

Please use TGML tage when posting table examples. As you can see, you data does not line up.

Second, you are using a REPORT to analyse you data. It like shooting yourself in the foot!

That being said, as PHV suggested, why not AutoFilter on the Advisor, then copy and paste special VALUES. Pretty simple!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffwest1]

First, i am using a report to export my data into a usable format, as detailed above, so hardly shooting myself in the foot, more getting the data in the least unweildy format possible, if it wasn't a pain in the backside i would prefer to be using BCP or similar from the database, unfortunatly the provider of the database does not permit bcp or similar from it, this is a situation for now I can do nothing about.

Secondly, I haven't used VBA with Excel for a very long time, i am trying to re get to grips with it, I asked the question can you use Autofilter via VBA? you say this is pretty simple, great, my question was how.

Thirdly i have a program that will pull all the agregated data i need into one format, in one table in the database, this is what i am exporting against.

I have added some new code to mine, but for some reason i cannot get it to actually filter the correct sheet.

Sub FindId()

Dim Var1 As String
Dim CopyRange As Range

Var1 = 0
Var1 = Cells(4, 2) 'Find Advisor Name

Workbooks.Open Filename:="c:\Scorecardtest\Neet.xls" 'Open Consolidated workbook

With Sheet1
.AutoFilterMode = False
.Range("A1:G16").AutoFilter
.Range("A1:G16").AutoFilter Field:=1, Criteria1:=Var1

Set CopyRange = Range("A1:N9") 'Copy Data

CopyRange.Copy Destination:=Worksheets("Summary").Range("A1") 'Paste Range to spreadsheet
End With

Workbooks("Neet.xls").Close 'Close Consolidated Workbook

End Sub

What i don't understand, is if i am specifying the workbook in the open command, why this doesn't retain the focus.

Adding a msgbox to give me the open workbook and open sheet, the workbook is correct, however the sheet is always the one that is running the code.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

I'd move heaven and earth to get to the source data and report for each Advisor, rather than messing with a report. But maybe you simply cannot.

Secondly, I haven't used VBA with Excel for a very long time, i am trying to re get to grips with it, I asked the question can you use Autofilter via VBA? you say this is pretty simple, great, my question was how.

Turn on your macro recorder and record the processes for...

1. turning on the auto filter.

2. filtering one item

3. copying the Current Region and pasting to wherever.

Then make a list of Advisors using either the Acvanced Filter -- UNIQUE option or MS Query, using a Select DISTINCT query.

Loop thru the list of unique value, filtering & copying.

Post back with your recorded code or any other specific questions you might have.

What i don't understand, is if i am specifying the workbook in the open command, why this doesn't retain the focus.

What do you mean by "retain focus?" When you open that workbook, it is ACTIVE. My question would be, do you expect the With Sheet1 and following, to refer to THAT workbook, or some other workbook? It seems to me that you probably have some problems referencing the proper objects. With multiple workbooks, you need to carry the workbook reference for ALL objects.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Yes, you can do AutoFilter in VBA.

Turn on your macro recorder (Tools > Macros > Record New Macro). Then AutoFilter on the name, copy all rows (excel will automatically only copy the visible rows) and paste to another sheet.

Turn off the recorder and observe the code that was generated.

Post the code back here if you need help cleaning it up.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Bong]

I agree that the source data is preferable but this seems to be a pretty common, if inefficient, business constraint.

It looks like your spreadsheet will always be "rectangular", that is, you will always have contiguous rows with all the same number of columns. In that caseusedrange should reliably return the entire set of data. Then you'll want to remove the first row. Then, for each row in that range, if the first cell is the name you're looking for, then you use that row.

Now, either you have a list of names you're looking for or, as suggested by the autofilter discussion, you want to aggregate the data for each name in the list, likely the latter. I think you can embed a while loop that searches for a name change inside a for loop that marches down the rows of usedrange exept the first row.

So,

rngA = sheet1.usedrange
r1=rngA.rows(1).row+1
r2=rngA.rows.count
advisor=sheet1.cells(r1,1)
for r =r1 to r2
   while r.cells(1).value=advisor
      'whatever
      
   loop
   advisor=r.cells(1).value
next

_________________
Bob Rashkin

 

[jeffwest1]

The source data is coming from a program written by our software supplier to aggregate data every month.

From this I am pulling out data for all advisors from 5 offices in the 8 soon 9 target areas for reporting, I cannot simply extract the data that i need at advisor level without doing it individually from this program, so i am exported from this into a database table.

From this table i am pulling out the already aggregated data, i can try and pull each advisor out from this via a crystal report in to seperate spreadsheets, which then feed into another speadsheet which will have additional data added by team managers,this data includes sickness and other absence, other key compatence's scores, as well as target levels for the values i am pulling from the database.

This doesn't account for staff leaving or starting, each of whom will have to have an additional feeded spreadsheet created.

I would love to just be having to pull the data out into 200 spreadsheets as this would be easier, but i cant because of the additional external requirements on the advisor level spreadsheet.

See above for how i set up my code for the autofilter, as i said i have got this bit working, it is just not creating the filter on the correct sheet.

I may need to look at naming each sheet rather than just having sheet1,sheet2 etc etc....

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

You have not explained

1. where YOUR source data (the report) is (workbook/sheet)

2. where each copy is going.

Exactly what is happening when you run your code? What error on what statement.

Not knowing the specifics, here is some code, GUESSING at some of the answers...

Sub FindId()
    Dim wbNEET As Workbook
    Dim r As Range
    
    Set wbNEET = Workbooks.Open(Filename:="c:\Scorecardtest\Neet.xls")  'Open Consolidated workbook
    
    With wbNEET.Sheet1
        .AutoFilterMode = False
        .Range("A1").AutoFilter
        
        For Each r In Range(YourUniqueAdvisorList)
            .Range("A1").AutoFilter Field:=1, Criteria1:=r.Value
        
            .Range("A1:N9").Copy _
                Destination:=.Worksheets("Summary").Range("A1") 'Paste Range to spreadsheet
        Next
        
    End With
    
    
    wb.Save
    wb.Close

End Sub

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jeffwest1]

Ok, adapting the code you sent to this

Sub Findid()

Dim wbNEET As Workbook
Dim r As Range
Dim Var1 As String

Var1 = 0
Var1 = Cells(4, 2) 'Find Advisor Name

Set wbNEET = Workbooks.Open(Filename:="c:\Scorecardtest\Neet.xls") 'Open Consolidated workbook

With wbNEET.Output
.AutoFilterMode = False
.Range("A1").AutoFilter

For Each r In Range(Yvar1)
.Range("A1").AutoFilter Field:=1, Criteria1:=r.Value

.Range("A1:N9").Copy _
Destination:=.Worksheets("Summary").Range("A1") 'Paste Range to spreadsheet
Next

End With


wb.Save
wb.Close



End Sub

When stepping through the code it errors at the 'With wbNEET.Output' line with 'Object does not support this property or method'

I have tried just using Sheet2 after the wbNEET as this is the sheet, as well as what i named it to, it opens the file no problem, so it is not that.

--------------------------------
Luke: But tell me why I can't...
Yoda: No, no, there is no why. Nothing more will I teach you today. Clear your mind of questions.

 

[SkipVought]

What is Output???

You have not defined this OBJECT or SET an instance.

If a sheet name, then ...

   With wbNEET.Sheets("Output")

Now, Sheets("Output") is an object.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]