Previous Topic Next Topic

Copy data from one excel sheet based on type

Closed
Exuser - May 30, 2010 at 01:13 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - 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

5 responses

Answer 1 / 5
windowsxp
Sep 6, 2011 at 02:43 AM
DEAR venkat,
how to run this macro?
can you give some steps?
3
windowsxp
Sep 6, 2011 at 03:19 AM
i did start a record macro, edit then copy pasted this,,,works fine,,
0
Answer 2 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
May 30, 2010 at 09:25 PM
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
Answer 3 / 5
windowsxp
Sep 6, 2011 at 03:18 AM
its working now. can anyone to help to update/refresh this sheets saswell?
0
Answer 4 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 6, 2011 at 04:36 AM
"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"
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 6, 2011 at 04:40 AM
"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.
0