Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
Hi, i have this code:
Sub Copy_A_B() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="A" rngDatabase.CurrentRegion.Copy Sheets("CompanyA").Paste Sheets("CompanyA").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="B" rngDatabase.CurrentRegion.Copy Sheets("CompanyB").Paste Sheets("CompanyB").Range("a1") Application.CutCopyMode = False rngDatabase.AutoFilter End Sub I need this code to be a little bit modified. Every day i will change the content of sheet 1, so i need the code to keep adding the content in CompanyA sheet and CompanyB sheet. At the end of the month i need to have in CompanyA and CompanyB sheets, the entry from all the days of the month. Can this be done? Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
I wrote you an email.
Thanks for your help! "Don Guillett" wrote: Although I would write it this way, why doesn't it do what you want since it filters ALL A each time. If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. Sub Copy_A_BNewA() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") With rngDatabase.CurrentRegion .AutoFilter Field:=1, Criteria1:="A" .Copy Sheets("A").Range("a1") .AutoFilter Field:=1, Criteria1:="B" .Copy Sheets("B").Range("a1") Application.CutCopyMode = False .AutoFilter End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this code: Sub Copy_A_B() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="A" rngDatabase.CurrentRegion.Copy Sheets("CompanyA").Paste Sheets("CompanyA").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="B" rngDatabase.CurrentRegion.Copy Sheets("CompanyB").Paste Sheets("CompanyB").Range("a1") Application.CutCopyMode = False rngDatabase.AutoFilter End Sub I need this code to be a little bit modified. Every day i will change the content of sheet 1, so i need the code to keep adding the content in CompanyA sheet and CompanyB sheet. At the end of the month i need to have in CompanyA and CompanyB sheets, the entry from all the days of the month. Can this be done? Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
Sub TransferMonthDataSAS() 'SalesAidSoftware
mc = Cells.Find(What:=Range("b3"), After:=Range("b3"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Column 'MsgBox mc For i = 2 To 8 'col H mr = Cells.Find(What:=Cells(5, i), After:=Range("a6"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Row 'MsgBox c & mr Cells(6, i).Resize(7).Copy Cells(mr + 2, mc) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... I wrote you an email. Thanks for your help! "Don Guillett" wrote: Although I would write it this way, why doesn't it do what you want since it filters ALL A each time. If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. Sub Copy_A_BNewA() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") With rngDatabase.CurrentRegion .AutoFilter Field:=1, Criteria1:="A" .Copy Sheets("A").Range("a1") .AutoFilter Field:=1, Criteria1:="B" .Copy Sheets("B").Range("a1") Application.CutCopyMode = False .AutoFilter End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this code: Sub Copy_A_B() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="A" rngDatabase.CurrentRegion.Copy Sheets("CompanyA").Paste Sheets("CompanyA").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="B" rngDatabase.CurrentRegion.Copy Sheets("CompanyB").Paste Sheets("CompanyB").Range("a1") Application.CutCopyMode = False rngDatabase.AutoFilter End Sub I need this code to be a little bit modified. Every day i will change the content of sheet 1, so i need the code to keep adding the content in CompanyA sheet and CompanyB sheet. At the end of the month i need to have in CompanyA and CompanyB sheets, the entry from all the days of the month. Can this be done? Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
IGNORE. Posted to wrong thread
Sub TransferMonthDataSAS() 'SalesAidSoftware mc = Cells.Find(What:=Range("b3"), After:=Range("b3"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Column 'MsgBox mc For i = 2 To 8 'col H mr = Cells.Find(What:=Cells(5, i), After:=Range("a6"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Row 'MsgBox c & mr Cells(6, i).Resize(7).Copy Cells(mr + 2, mc) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Sub TransferMonthDataSAS() 'SalesAidSoftware mc = Cells.Find(What:=Range("b3"), After:=Range("b3"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Column 'MsgBox mc For i = 2 To 8 'col H mr = Cells.Find(What:=Cells(5, i), After:=Range("a6"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Row 'MsgBox c & mr Cells(6, i).Resize(7).Copy Cells(mr + 2, mc) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... I wrote you an email. Thanks for your help! "Don Guillett" wrote: Although I would write it this way, why doesn't it do what you want since it filters ALL A each time. If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. Sub Copy_A_BNewA() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") With rngDatabase.CurrentRegion .AutoFilter Field:=1, Criteria1:="A" .Copy Sheets("A").Range("a1") .AutoFilter Field:=1, Criteria1:="B" .Copy Sheets("B").Range("a1") Application.CutCopyMode = False .AutoFilter End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this code: Sub Copy_A_B() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="A" rngDatabase.CurrentRegion.Copy Sheets("CompanyA").Paste Sheets("CompanyA").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="B" rngDatabase.CurrentRegion.Copy Sheets("CompanyB").Paste Sheets("CompanyB").Range("a1") Application.CutCopyMode = False rngDatabase.AutoFilter End Sub I need this code to be a little bit modified. Every day i will change the content of sheet 1, so i need the code to keep adding the content in CompanyA sheet and CompanyB sheet. At the end of the month i need to have in CompanyA and CompanyB sheets, the entry from all the days of the month. Can this be done? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro help
Ok.
"Don Guillett" wrote: IGNORE. Posted to wrong thread Sub TransferMonthDataSAS() 'SalesAidSoftware mc = Cells.Find(What:=Range("b3"), After:=Range("b3"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Column 'MsgBox mc For i = 2 To 8 'col H mr = Cells.Find(What:=Cells(5, i), After:=Range("a6"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Row 'MsgBox c & mr Cells(6, i).Resize(7).Copy Cells(mr + 2, mc) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Sub TransferMonthDataSAS() 'SalesAidSoftware mc = Cells.Find(What:=Range("b3"), After:=Range("b3"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Column 'MsgBox mc For i = 2 To 8 'col H mr = Cells.Find(What:=Cells(5, i), After:=Range("a6"), LookIn:=xlFormulas, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Row 'MsgBox c & mr Cells(6, i).Resize(7).Copy Cells(mr + 2, mc) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... I wrote you an email. Thanks for your help! "Don Guillett" wrote: Although I would write it this way, why doesn't it do what you want since it filters ALL A each time. If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. Sub Copy_A_BNewA() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") With rngDatabase.CurrentRegion .AutoFilter Field:=1, Criteria1:="A" .Copy Sheets("A").Range("a1") .AutoFilter Field:=1, Criteria1:="B" .Copy Sheets("B").Range("a1") Application.CutCopyMode = False .AutoFilter End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "puiuluipui" wrote in message ... Hi, i have this code: Sub Copy_A_B() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="A" rngDatabase.CurrentRegion.Copy Sheets("CompanyA").Paste Sheets("CompanyA").Range("a1") rngDatabase.CurrentRegion.AutoFilter Field:=1, _ Criteria1:="B" rngDatabase.CurrentRegion.Copy Sheets("CompanyB").Paste Sheets("CompanyB").Range("a1") Application.CutCopyMode = False rngDatabase.AutoFilter End Sub I need this code to be a little bit modified. Every day i will change the content of sheet 1, so i need the code to keep adding the content in CompanyA sheet and CompanyB sheet. At the end of the month i need to have in CompanyA and CompanyB sheets, the entry from all the days of the month. Can this be done? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |