copy data from one excel sheet based on typeClosed

Question

Hello, 

I have a scenario where there is a main worksheet in the workbook which contains information in the below format

Item code           Item Name      value           Category / Type

I0001                 Keyboard        1000            Computers
I0002                 Mouse             800              Computers
I3456                 TV                    2000           Homeappliance
I4566                 Microwave       4000           Kitchenitem

Now after entering the data in the main sheet user would like to save the data into other excel sheets with details for each item

In this case There would be three additional worksheets called "Computers", "Homeappliance", "Kitchenitem".
There should be a "save" button in the first sheet and on clicking on Save button the basic information entered in the first excel sheet should be saved into respective excel sheets based on the category, so that user can go and provide more details as per the item category against each item. Also if the item is already there in the respective sheets and any modification made in the first sheet should be upadated in the respective sheets

Please let me know How this can be done in MS Excel

Thanks a lot

windowsxp · Accepted Answer

DEAR venkat,
how to run this macro?
can you give some steps?

venkat1926 · Answer

you main data is in Sheet1 and if it is not so modify the macro wherever Sheet1 comes. the name of the sheet should be exact and case sensitive insert 3 sheets and names them Computers Homeappliance Kitchenitem agains spelling should be exact and case sensitive. better copy the names in column D and paste in the sheet tabs. now run the macro test (the second macro undo undoes the macro result) Dim j As Integer, k As Integer Dim r As Range, r1 As Range, dest As Range Dim r2 As Range, c2 As Range, x As String Dim r3 As Range Sub test() Worksheets("sheet1").Activate ActiveSheet.AutoFilterMode = False Set r = Range("A1").CurrentRegion Set r1 = Range(Range("d1"), Range("d1").End(xlDown)) Set dest = Range("A1").End(xlDown).Offset(5, 0) r1.AdvancedFilter action:=xlFilterCopy, copytorange:=dest, unique:=True Set r2 = Range(dest.Offset(1, 0), dest.End(xlDown)) For Each c2 In r2 x = c2.Value r.AutoFilter field:=4, Criteria1:=x Set r3 = Range(Range("A2"), Range("A2").End(xlDown).End(xlToRight)) r3.Cells.SpecialCells(xlCellTypeVisible).Copy With Worksheets(x) .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial End With ActiveSheet.AutoFilterMode = False Next c2 j = Worksheets.Count Set r = Range("A1:D1") r.Copy For k = 1 To j If Worksheets(k).Name <> "Sheet1" Then Worksheets(k).Range("A1").PasteSpecial End If Next k End Sub Sub undo() j = Worksheets.Count For k = 1 To j If Worksheets(k).Name <> "Sheet1" Then Worksheets(k).Cells.Clear End If Next k Worksheets("Sheet1").Activate Set r = Range("A1").End(xlDown).Offset(5, 0) Set r1 = Range(r, r.End(xlDown)) r1.Cells.Clear End Sub

windowsxp · Answer

its working now. can anyone to help to update/refresh this sheets saswell?

venkat1926 · Answer

"can anyone to help to update/refresh this sheets saswell?"

DO THIS EXPERIMENT IN A COPY OF THE FILE AND NOT IN THE ORIGINAL FILE

not clear. Perhaps you want to run "undo' first and then run " test"

venkat1926 · Answer

"i did start a record macro, edit then copy pasted this,,,works fine,,"

MACROS are parked in modules (there are some other called event codes)

to park a macro 

open vb editor F11
click control R
on the left hand side you get project window where all the open workbooks(excel files) are listed. you highlight this relevant file and click "insert" in the vb ediltor window and click "module" there you copy paste the macro. 

this is for your general information.

Copy data from one excel sheet based on type

5 responses

Similar discussions

Subscribe To Our Newsletter!