Pull Specific Information from another file

[jaihawk8]

I am developing a commissions worksheet for our Sales Team. In one worksheet, I have our commissions log which contains the following information in each column:

Date of Sale
Sales Rep
Customer Name
List Price
Sales Price
Commission
Payroll Date

Each Sales Rep has their own spreadsheet so that we can send them their information individually. Normally, I would filter the commission log for a specific Sales Rep and Payroll date and then cut and paste that information into the Sales Reps' Spreadsheet. With 45 Sales Reps, this can get a bit tedious.

Is there any way to have the Sales Reps spreadsheet only pull their information from the Commission Log?

Thanks!

 

[Nine Zero]

very do-able just need more information... what is the name structure of the individual spreadsheets? is it for example SalesRepName.xlsx or something like that? Also what column is the sales rep name in the commission log?

 

[jaihawk8]

very do-able just need more information... what is the name structure of the individual spreadsheets? is it for example SalesRepName.xlsx or something like that? Also what column is the sales rep name in the commission log?

https://www.dropbox.com/sh/i7mhlku4q36t5qm/AAAIFr5s2aZYyJKLuDJWc32wa?dl=0

I put an example of both files in this share directory (hopefully I did that right).

So the Sales Commission Log contains all of the information. Each rep has their own sheet. For this illustration, I want the Team Hayden - Chad Pederson file to populate with only his information from the Sales Commission Log, Column E.

 

[Fluff]

Your link comes back as "This folder is empty"

 

[jaihawk8]

Your link comes back as "This folder is empty"

Shoot! How about now?

https://www.dropbox.com/sh/i7mhlku4q36t5qm/AAAIFr5s2aZYyJKLuDJWc32wa?dl=0

 

[Fluff]

That works

 

[jaihawk8]

Curious if anyone has anything for this. I found some code online that works if I am in the same workbook:

Sub TestVB()


Dim i, LastRow


LastRow = Sheets("Q1Detail").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A2:AB1500").Cells.Delete
For i = 2 To LastRow
If Sheets("Q1Detail").Cells(i, "E").Value = Sheets("Main").Range("A2").Value Then
Sheets("Q1Detail").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

Unfortunately, I need it to pull the Q1Detail information from another file. I just don't know how to reference that file.

 

[Nine Zero]

Curious if anyone has anything for this. I found some code online that works if I am in the same workbook:

Sub TestVB()


Dim i, LastRow


LastRow = Sheets("Q1Detail").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A2:AB1500").Cells.Delete
For i = 2 To LastRow
If Sheets("Q1Detail").Cells(i, "E").Value = Sheets("Main").Range("A2").Value Then
Sheets("Q1Detail").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

Unfortunately, I need it to pull the Q1Detail information from another file. I just don't know how to reference that file.

try this out

Dim myBook as Workbook
set myBook = Workbooks.Open("insert yuor filepath here ending with .xlsx")
'then you can call it with the variable name like
LastRow = myBook.Sheets(Q1Detail").blah blah blah

 

[jaihawk8]

try this out

Dim myBook as Workbook
set myBook = Workbooks.Open("insert yuor filepath here ending with .xlsx")
'then you can call it with the variable name like
LastRow = myBook.Sheets(Q1Detail").blah blah blah

Thank you for that. This is what I changed the code to:

Sub TestVB()


Dim myBook As Workbook
Set myBook = Workbooks.Open("C:\Users\jasonb\Documents\TestData.xlsx")


Dim i, LastRow


LastRow = myBook.Sheets("Q1Detail").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A2:AB1500").Cells.Delete
For i = 2 To LastRow
If myBook.Sheets("Q1Detail").Cells(i, "E").Value = Sheets("Main").Range("A2").Value Then
myBook.Sheets("Q1Detail").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

However, I am getting a Run-time error '9": Subscript out of range on this line:

Sheets("Sheet2").Range("A2:AB1500").Cells.Delete

 

[Nine Zero]

Ahh ok what you are missing is a reference to the current book where your code is... think of it like you told the code to go to the newly opened workbook but you never told it to go back to the other book so it has no idea where Sheet2 is at....simply create another variable for that workbook and add that reference

Dim currentBook as Workbook
set currentBook = ThisWorkbook
'then in your code call the variable
[B][COLOR=#ff0000]currentBook[/COLOR][/B].Sheets("Sheet2").Range("A2:AB1500").Cells.Delete
myBook.Sheets("Q1Detail").Cells(i, "E").EntireRow.Copy  Destination:=[B][COLOR=#ff0000]currentBook[/COLOR][/B].Sheets("Sheet2").Range("A" &  Rows.Count).End(xlUp).Offset(1)
'something like that

or something like this if you dont wanna do the setting of the variable... im just used to setting everything in this form lol

[B][COLOR=#ff0000]ThisWorkbook[/COLOR][/B].Sheets("Sheet2").Range("A2:AB1500").Cells.Delete
myBook.Sheets("Q1Detail").Cells(i, "E").EntireRow.Copy  Destination:=[B][COLOR=#ff0000]ThisWorkbook[/COLOR][/B].Sheets("Sheet2").Range("A" &  Rows.Count).End(xlUp).Offset(1)
'something like that