| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering data
Dear sir,with my regards,like to thank you for your help and wonderful solution which encouraged me to send another file asking for another help to transfer datas from main sheet to (12) sheets being the twelve months (without duplicates the transferred data). Many thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=145| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
I just checked to make sure duplicate invoice numbers where not copied
Sub transferdata() With Sheets("Main") RowCount = 5 Start = RowCount LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While RowCount <= LastRow If .Range("C" & RowCount) < "" Then Invoice = .Range("C" & RowCount) End If If RowCount = LastRow Or _ .Range("B" & (RowCount + 1)) Then MyMonth = Month(.Range("B" & RowCount)) With Sheets(MyMonth) Set c = .Columns("C").Find(what:=Invoice, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then NewRow = .Range("B" & Rows.Count).End(xlUp).Row + 1 Sheets("main").Rows(Start & ":" & RowCount).Copy _ Destination:=.Rows(NewRow) Start = RowCount + 1 End If End With RowCount = RowCount + 1 End If RowCount = RowCount + 1 Loop End With End Sub "tofimoon4" wrote: Dear sir,with my regards,like to thank you for your help and wonderful solution which encouraged me to send another file asking for another help to transfer datas from main sheet to (12) sheets being the twelve months (without duplicates the transferred data). Many thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=145| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
I found some rrors. Didnt check the result very carefully the 1st time
Sub transferdata() Dim MyMonth As String For Each sht In Sheets If sht.Name < "main" Then sht.Rows("5:" & Rows.Count).Delete End If Next sht With Sheets("Main") RowCount = 5 Start = RowCount LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While RowCount <= LastRow If .Range("C" & RowCount) < "" Then Invoice = .Range("C" & RowCount) End If If RowCount = LastRow Or _ .Range("C" & (RowCount + 1)) < "" Then MyMonth = Month(.Range("B" & RowCount)) With Sheets(MyMonth) Set c = .Columns("C").Find(what:=Invoice, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then NewRow = .Range("B" & Rows.Count).End(xlUp).Row + 1 If NewRow = 4 Then NewRow = 5 End If Sheets("main").Rows(Start & ":" & RowCount).Copy _ Destination:=.Rows(NewRow) Start = RowCount + 1 End If End With End If RowCount = RowCount + 1 Loop End With End Sub "tofimoon4" wrote: Dear sir,with my regards,like to thank you for your help and wonderful solution which encouraged me to send another file asking for another help to transfer datas from main sheet to (12) sheets being the twelve months (without duplicates the transferred data). Many thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=145| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Sub transferdata()
Dim MyMonth As String For Each sht In Sheets If sht.Name < "main" Then sht.Rows("5:" & Rows.Count).Delete End If Next sht With Sheets("Main") RowCount = 5 Start = RowCount LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While RowCount <= LastRow If .Range("C" & RowCount) < "" Then Invoice = .Range("C" & RowCount) End If If RowCount = LastRow Or _ .Range("C" & (RowCount + 1)) < "" Then MyMonth = Month(.Range("B" & RowCount)) With Sheets(MyMonth) Set c = .Columns("C").Find(what:=Invoice, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then NewRow = .Range("B" & Rows.Count).End(xlUp).Row + 1 If NewRow = 4 Then NewRow = 5 End If Sheets("main").Rows(Start & ":" & RowCount).Copy _ Destination:=.Rows(NewRow) Start = RowCount + 1 End If End With End If RowCount = RowCount + 1 Loop End With End Sub "tofimoon4" wrote: Dear sir,with my regards,like to thank you for your help and wonderful solution which encouraged me to send another file asking for another help to transfer datas from main sheet to (12) sheets being the twelve months (without duplicates the transferred data). Many thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=145| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Joel,
Did we read the same post? -- Don Guillett Microsoft MVP Excel SalesAid Software "Joel" wrote in message ... I found some rrors. Didnt check the result very carefully the 1st time Sub transferdata() Dim MyMonth As String For Each sht In Sheets If sht.Name < "main" Then sht.Rows("5:" & Rows.Count).Delete End If Next sht With Sheets("Main") RowCount = 5 Start = RowCount LastRow = .Range("A" & Rows.Count).End(xlUp).Row Do While RowCount <= LastRow If .Range("C" & RowCount) < "" Then Invoice = .Range("C" & RowCount) End If If RowCount = LastRow Or _ .Range("C" & (RowCount + 1)) < "" Then MyMonth = Month(.Range("B" & RowCount)) With Sheets(MyMonth) Set c = .Columns("C").Find(what:=Invoice, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then NewRow = .Range("B" & Rows.Count).End(xlUp).Row + 1 If NewRow = 4 Then NewRow = 5 End If Sheets("main").Rows(Start & ":" & RowCount).Copy _ Destination:=.Rows(NewRow) Start = RowCount + 1 End If End With End If RowCount = RowCount + 1 Loop End With End Sub "tofimoon4" wrote: Dear sir,with my regards,like to thank you for your help and wonderful solution which encouraged me to send another file asking for another help to transfer datas from main sheet to (12) sheets being the twelve months (without duplicates the transferred data). Many thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=145| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Dear friends,many thanks for help. 1-Mr.Don : with my appreciation to your solution ,there is only one note about the ( total ) column which doesnot appear in( main )sheet and all( months sheets) transferred invoices. -My e.mail is ( ). 2-Mr.Joel : with my admiration in your solution ,i think there is a problem in A- ( sheet 12 ) after transferring data B- the months sheets after 5 sheet , if you can check the result of transferring the data . Hopping to recieve any amendments for the codes which help me to reach the target. My regards. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
I don't understand your response... -- donaldb36 ------------------------------------------------------------------------ donaldb36's Profile: http://www.thecodecage.com/forumz/member.php?userid=143 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Dear sir,you are hero,i am so lucky to see you on this wonderful forum,thank you very much for help,everything is excellent. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Glad to help
-- Don Guillett Microsoft MVP Excel SalesAid Software "tofimoon4" wrote in message ... Dear sir,you are hero,i am so lucky to see you on this wonderful forum,thank you very much for help,everything is excellent. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Dear sir,with my apology,i found after some expriences that the sheets (6,7,8,10) which i coloured in yellow in attached file have a mistake. Asking you kindly to take a look and modify. thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring1.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=146| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Only filters if month in col J
Sub clearsheets() For Each ws In Worksheets If UCase(ws.Name) < "MAIN" Then ws.Rows("5:1000").ClearContents Next End Sub Sub filterbymonthandcopytosheetsSAS() Application.ScreenUpdating = False clearsheets Range("b4") = 0 lr = Cells(Rows.Count, "b").End(xlUp).Row For i = 5 To lr Cells(i, "J").Value = Month(Cells(i, 2)) Next i With Range("a2:j" & lr) For i = 1 To 12 If Application.CountIf(Columns("j"), i) 0 Then .AutoFilter Field:=10, Criteria1:=i Range(Cells(5, "b"), Cells(lr, "i")).Copy With Sheets(CStr(i)).Range("b5") .PasteSpecial Paste:=xlPasteFormats .PasteSpecial Paste:=xlPasteValues End With End If Next i .AutoFilter End With Columns("j").ClearContents Application.ScreenUpdating = True End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "tofimoon4" wrote in message ... Dear sir,with my apology,i found after some expriences that the sheets (6,7,8,10) which i coloured in yellow in attached file have a mistake. Asking you kindly to take a look and modify. thanks in advance. +-------------------------------------------------------------------+ |Filename: Transferring1.zip | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=146| +-------------------------------------------------------------------+ -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Thank you my sir , you are genius really. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Transfering data
Glad to help -- Don Guillett Microsoft MVP Excel SalesAid Software "tofimoon4" wrote in message ... Thank you my sir , you are genius really. -- tofimoon4 ------------------------------------------------------------------------ tofimoon4's Profile: http://www.thecodecage.com/forumz/member.php?userid=138 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=100128 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| transfering data in another column entering more sorted data | Excel Worksheet Functions | |||
| transfering all data. | Excel Programming | |||
| Transfering Data from Several Sheets to another | Excel Worksheet Functions | |||
| transfering data | Excel Programming | |||
| Transfering dynamic data | Excel Programming | |||
![tofimoon4[_14_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
