Solved: Transferring data

[tofimoon4]

Dear sir,with best regards,i have in enclosed file a (very nice ) code for transferring data from main sheet to (12) sheets being the (12) months of the year ,only one problem caused some disturbance to me .
When i press the button for transferring ,everything is ok except the data after (5)th month which appears in (12)th month as mentioned in the attached file.
I need your help please by some modification for the code from our experts to work well .
Thanks in advance.
Note: i have written about this problem to my wonderful forum (excel forum) but i think our experts there are so busy .

[mdmackillop]

Try

LastRow = .Range("B" & Rows.Count).End(xlUp).Row

[nb-]

[Edit - mdmackillop's suggestion above is better.]

The problem is actually that your 'very nice' code appears to copy the last invoice in the list to the last sheet irrespective of what month it is in.

The problem stems from your treatment of blank cells to find the last cell, and that if you get the Month(BlankCell) it returns 12, hence why it was copied to the last sheet.

Your use of LastRow = .Range("A" & Rows.Count).End(xlUp).Row was not giving you the 'lastrow' you wanted, it counts rows with formulas in even if they equal "", you want the last non blank row.

See amended code below;


Option Explicit

Sub transferdata()

Dim MyMonth As String
Dim ws As Worksheet
Dim i As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim NewRow As Long
Dim Invoice
Dim c As Object

'// Delete all rows from 5 down in all sheets apart from the main sheet.
For Each ws In Sheets
If ws.Name <> "main" Then
ws.Rows("5:" & Rows.Count).Delete
'Debug.Print ws.Name & " deleted"
End If
Next ws

With Sheets("Main")

FirstRow = 5 '// FirstRow row
LastRow = .Range("A" & Rows.Count).End(xlUp).Row '// find last used row in column A

'Debug.Print "LastRow"; LastRow; "'// This isn't the last row you really want."

For i = LastRow To FirstRow Step -1
If .Range("A" & i).Value = "" Then
LastRow = LastRow - 1
Else
Exit For
End If
Next i

'Debug.Print "New LastRow"; LastRow; "'// This is the last row you actually want."

For i = FirstRow To LastRow

If .Range("C" & i) <> "" Then
Invoice = .Range("C" & i)
End If

If i = LastRow Or .Range("C" & (i + 1)) <> "" Then '// if last row or cell below not blank

If .Range("B" & i).Value <> "" Then
MyMonth = Month(.Range("B" & i))
End If

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(FirstRow & ":" & i).Copy _
Destination:=.Rows(NewRow)
FirstRow = i + 1
End If

End With
End If
Next i

End With

Set c = Nothing

End Sub

[tofimoon4]

Dear sir,many thanks for your interest,i am so happy to reach the target at last.
My best regards.

[tofimoon4]

Dear sir,with best regards,i have in enclosed file a (very nice ) code for transferring data from main sheet to (12) sheets being the (12) months of the year ,only one problem caused some disturbance to me .
When i press the button for transferring ,everything is ok except the data after (5)th month which appears in (12)th month as mentioned in the attached file.
I need your help please by some modification for the code from our experts to work well .
Thanks in advance.
Note: i have written about this problem to my wonderful forum (excel forum) but i think our experts there are so busy .

[geekgirlau]

Hi tofimoon4 - I've marked this thread as "solved". You can do this under "Thread Tools" at the top of the page.