Fetching_Records_From_Another-sheet

[Ravindra]

Dear All,


I want to have visual basic controlles included in sheet as per my requriment being indicated on sheet(MYASSIGN).


Synopsis:- MYASSIGN sheet will contain record for specified column which are BATCH NUMBER, #IMAGES, #COMP, #CLAIMS, INDEXER, CLAIM TYPE and CLIENT. All records will be fetched from another sheet(Assignments1.xls,Assignments2.xls,,,,Assignments3.xls).

I have given three user controls according me, which are LABEL,TEXBOX,BUTTON.

If user input EMP1 in text box then on click event of button sheet MYASSIGN.XLS sheet wiill fetch records from Assignments1(if date is 1) for EMP1.


Note:- Assignments name will be based on date which is named accoring daily bases inventory record.


MYASSIGN:- I basically show you a snapshot for fetching reocord for EMP1.


Here given link for both sheets:-

MYASSIGN.xls- http://www.2shared.com/document/0D-4C0Ng/MYASSIGN.html


Assignments1.xls- http://www.2shared.com/document/JZvkqoSJ/Assignments_1.html


If any concern recarding my question please ask.


Thanks & regards,

Ravindra Bisht

 

[Ravindra]

please try to help me in this concern,

 

[kaushik03]

Hi Ravindra,


Can you plz download the files from the below link and check if it is fine.


http://speedy.sh/WZRxm/MYASSIGN-1.xls


http://speedy.sh/M3evK/Assignments-1-1.xls


Instructions:


1)Save both the workbooks("MYASSIGN-1.xls" and "Assignments 1-1.xls") in your desktop.


2)Now open "MYASSIGN-1.xls" workbook


3)You have said that data needs to be copied as per user's input at textbox but I really do not understand the need to use textbox feature. Hence, as of now, I have done this with combobox which is more user friendly method and chances of committing error is less as user do not need to type the employee code. As you select the employee code from drop down, the code will trigger and the desired work will be executed.In each sheet, I have placed this combobox and assign macro to it. As of now, the filling of employee code is not dynamic.If you are fine with this combobox method, we can incorporate this dynamic feature accordingly.


4)The code is made to copy data from "Assignments 1-1.xls" workbook to "MYASSIGN-1.xls" workbook


5)The macro will first open the "Assignments 1-1.xls" workbook, copy the data for the employee selected by the user and paste it in the active sheet of "MYASSIGN-1.xls" workbook, and then close the "Assignments 1-1.xls" workbook.


Code is:

.......................................................


Sub CopyData()


'Assign variables


Dim path As String

Dim TempVal As String


Application.ScreenUpdating = False

Application.DisplayAlerts = False


'define the workbook location


path = ThisWorkbook.path & ""


'Store the user selected employee code temporarily


TempVal = ActiveSheet.Range("R1").Value


'Open the "Assignments 1-1.xls" workbook


Application.Workbooks.Open (path & "Assignments 1-1.xls")


Workbooks("Assignments 1-1.xls").Activate


'Loop through the sheets of the active workbook, filter the data for the emp code selected by user, copy the data and paste it in "MYASSIGN-1.xls" workbook

For I = 1 To Worksheets.Count

Worksheets(I).Activate


'Remove filter if there filter mode is on


If ActiveSheet.AutoFilterMode = True Then

ActiveSheet.AutoFilterMode = False

End If

Range("A1").Select


Selection.AutoFilter


'In case the selected emp code does not exist in the sheet where macro is executing, there is no need to copy the data


On Error GoTo errhandler:


ActiveSheet.Range("$A$1:$I$65536").AutoFilter Field:=6, Criteria1:=TempVal


LstRowToCopy = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row


If LstRowToCopy > 1 Then


Range("A2:F" & LstRowToCopy).Copy

Workbooks("MYASSIGN-1.xls").Activate

Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select

ActiveSheet.Paste


Workbooks("Assignments 1-1.xls").Worksheets(I).Activate


Range("I2:I" & (LstRowToCopy)).Copy

Workbooks("MYASSIGN-1.xls").Activate

Range("G" & Range("G" & Rows.Count).End(xlUp).Row + 1).Select

ActiveSheet.Paste

End If


Workbooks("Assignments 1-1.xls").Activate

errhandler:


Next I


ActiveWorkbook.Save


ActiveWorkbook.Close


Workbooks("MYASSIGN-1.xls").Activate


Range("A1").Select


Application.ScreenUpdating = True

Application.DisplayAlerts = True


End Sub

 

[irisqueiroz]

Thanks, kaushik03!


It worked well

 

[kaushik03]

Hi irisqueiroz,


Glad to hear that it worked for you.


Please note in Chandoo.org, it is not advisable to do any cross posting. This post was started by Ravindra and we are still waiting for his reply.You sould reply this to your own post below here:


http://chandoo.org/forums/topic/autofilter-and-if-statement


Anyways, come back whenever needed...


Kaushik