Please help me Code this...

[uveenhacked]

i have tried recording a macro and assign an button for it but the code don't look good (lots of scroll and range selct) and in sometimes it end up with an error.

this is what i want to do...

-------

on button click...

save as new file name sequence number (store001.xls ...) on same folder

select sheet name "INV",

select range e3 to E65536

copy data and paste (hard code) to G3 to G65536 respectively

delete H3 - H65536 up to column AM3 to AM65536

select range G3 to G65536

copy data and paste (hard code) to H3 to H65536 respectively

select sheet name "01"

select range D3 to D65536
clear

select range H3 to H65536
clear

set all values of the range C3 to C65536 to 1

select H3

end

-------

hope you guys can help me, i am really new to this vba stuff.

Thanks in advance

 

[Zack Barresse]

Hi there,

Maybe something like this (I cleaned it up a little bit, took out those pesky selects)...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> reset_data()

    <SPAN style="color:#00007F">Dim</SPAN> INV <SPAN style="color:#00007F">As</SPAN> Worksheet, WS1 <SPAN style="color:#00007F">As</SPAN> Worksheet, SUM <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> Msg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> fName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Fn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, NewFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    Msg = "This will reset all data entry made, are you sure you want to continue?"
    <SPAN style="color:#00007F">If</SPAN> MsgBox(Msg, vbYesNo, "Sumagot ka!") = vbYes <SPAN style="color:#00007F">Then</SPAN>
        
        <SPAN style="color:#007F00">'save document before anything else</SPAN>
        ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
        ActiveWorkbook.Save
        
        <SPAN style="color:#007F00">'create new file</SPAN>
        strPath = ActiveWorkbook.Path
        Fn = ActiveWorkbook.FullName
        fName = Mid(Fn, Len(Fn) - 6, 3)
        fName = Format(fName + 1, "000")
        NewFileName = strPath & "\store" & fName & ".xls"
        ActiveWorkbook.SaveAs NewFileName
        
        <SPAN style="color:#007F00">'reset data</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> INV = Sheets("Inv")
        <SPAN style="color:#00007F">Set</SPAN> WS1 = Sheets("01")
        <SPAN style="color:#00007F">Set</SPAN> SUM = Sheets("summary")
        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
        <SPAN style="color:#00007F">With</SPAN> INV
            .Unprotect "password" <SPAN style="color:#007F00">'**</SPAN>
            .Range("E3:E65536").Copy
            .Range("G3").PasteSpecial xlPasteValues
            .Range("H3:AM65536").ClearContents
            .Range("G3:G65536").Cut
            .Range("H3").PasteSpecial xlPasteAll
            .Range("A1").Select <SPAN style="color:#007F00">'...don't really need</SPAN>
            .Protect "password" <SPAN style="color:#007F00">'**</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">With</SPAN> WS1
            .Unprotect "password" <SPAN style="color:#007F00">'**</SPAN>
            .Range("D3:D65536").ClearContents
            .Range("H3:H65536").ClearContents
            .Range("C3:C65536").Value = 1
            .Range("D3").Select <SPAN style="color:#007F00">'...don't really need</SPAN>
            .Protect "password" <SPAN style="color:#007F00">'**</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">With</SPAN> SUM
            .Unprotect "password" <SPAN style="color:#007F00">'**</SPAN>
            .PivotTables("PivotTable4").PivotCache.Refresh
            .PivotTables("PivotTable1").PivotCache.Refresh
            .Protect "password" <SPAN style="color:#007F00">'**</SPAN>
        WS1.Activate <SPAN style="color:#007F00">'...don't really need</SPAN>
        
        Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Else</SPAN>
        MsgBox "Mag isip muna bago pumindot ha...", vbInformation, "Naman!!!"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


Does this help?