macro help

  • Thread starter Thread starter puiuluipui
  • Start date Start date
P

puiuluipui

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!
 
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
 
I wrote you an email.
Thanks for your help!

Don Guillett said:
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
(e-mail address removed)
Click to expand...
 
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
 
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
 
Ok.

Don Guillett said:
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
(e-mail address removed)
Click to expand...
 
Back
Top