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!

 

[jaihawk8]

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

Perhaps I did something wrong. Here's what I have:

Sub TestVB()


Dim currentBook As Workbook
Set currentBook = ThisWorkbook
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
currentBook.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:=currentBook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

Getting an error now on:
LastRow = myBook.Sheets("Q1Detail").Range("A" & Rows.Count).End(xlUp).Row

 

[Nine Zero]

Perhaps I did something wrong. Here's what I have:

Sub TestVB()


Dim currentBook As Workbook
Set currentBook = ThisWorkbook
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
currentBook.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:=currentBook.Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

Getting an error now on:
LastRow = myBook.Sheets("Q1Detail").Range("A" & Rows.Count).End(xlUp).Row

not sure if this will fix it but try adding this on your Dim i, LastRow

Dim i, LastRow [COLOR=#ff0000][B]as Integer
[/B][/COLOR]

 

[jaihawk8]

not sure if this will fix it but try adding this on your Dim i, LastRow

Dim i, LastRow [COLOR=#ff0000][B]as Integer
[/B][/COLOR]

Unfortunately, that did not work. My original script worked, when it was in the same workbook. Still getting an error on this line:

LastRow = myBook.Sheets("Q1Detail").Range("A" & Rows.Count).End(xlUp).Row

Script currently written:

Sub NineZero()


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


Dim i, LastRow As Integer


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