Copy data from one excel sheet based on type [Closed]

Exuser - May 30, 2010 at 01:13 PM - Latest reply: venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen
- Sep 6, 2011 at 04:40 AM
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
See more 

6 replies

+3
Helpful
1
DEAR venkat,
how to run this macro?
can you give some steps?
Was this answer helpful?  
i did start a record macro, edit then copy pasted this,,,works fine,,
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - May 30, 2010 at 09:25 PM
0
Helpful
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
0
Helpful
its working now. can anyone to help to update/refresh this sheets saswell?
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 6, 2011 at 04:36 AM
0
Helpful
"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 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Sep 6, 2011 at 04:40 AM
0
Helpful
"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.