Data transfer to another sheet

[tumanovr]

Hi there

I have a problem which I do not know how to solve

I have Main sheet with products but from this list I need only specific products which are in Sheets April and March (also other months but at first this two).

I need that From the Main sheet the system checks the Items which are in April Sheet and puts the amount ordered from Main to April. If there is more then one order then they need to be saparated in sheet Aprir the first order amount goes to Order 1 the second to Order 2 and etc.

Can you please help to find a solution.

Excel template is attached.

Excel version is 2102 (Build 13801.21092Click to Run)

[Sintek]

Can't see this done with formulas...VBA option...
Will work for all...Jan to Dec...Provided that the sheet exists...

Please Login or Register  to view this content.

[kvsrinivasamurthy]

In B2 copy to full range for all sheets.

Please Login or Register  to view this content.

I found a problem in the file that Every time after clicking Calculation Options -->Automatic only calculation is done.

[tumanovr]

Hi

Thanks for the code. I have tried it and it works but the for some kind of reason when I put new data in the main sheet and run the code the data is transferred again the same as the first time and the old one stays so in the end I have the same data doubled. I need that the when data is inserted the system checks if it was already there and just inserts in the last row the new data.

Can you help with this, I have tried to modify the code but it still doesn't work.

Modified code:

Sub J3v16()
Dim Data, Rw, Col As Long, i As Long
Data = Sheets("Main").Cells(1).CurrentRegion
For i = 2 To UBound(Data)
If Evaluate("ISREF('" & "" & Data(i, 1) & "" & "'!A1)") = True Then
With Sheets("" & Data(i, 1) & "")
'Check if column exists in sheet
On Error Resume Next
Col = WorksheetFunction.Match(Data(i, 2), .Rows(1), 0)
On Error GoTo 0
If Col > 0 Then
Rw = Application.Match(Data(i, 3), .Range("A:A"), 0)
If Not IsError(Rw) Then
' Check if data already exists
On Error Resume Next
If WorksheetFunction.CountIf(.Columns(Col), Data(i, 6)) = 0 Then
Col = .Cells(Rw, .Columns.Count).End(xlToLeft).Column + 1
.Cells(Rw, Col).Value = Data(i, 6)
End If
On Error GoTo 0
Else
'If row does not exist, add data to first empty row in column
Rw = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Cells(Rw, 1).Value = Data(i, 3)
Col = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
.Cells(1, Col).Value = Data(i, 2)
.Cells(Rw, Col).Value = Data(i, 6)
End If
End If
End With
End If
Next i
End Sub

[Gregb11]

In B2, try this:

=IFERROR(TRANSPOSE(FILTER(Main!$D$2:$D$11,(Main!$C$2:$C$11=A2)*(Main!$A$2:$A$11=TEXTAFTER(CELL("filename",$A$1),"]")))),"")

copy down.

You can also then copy this to other sheets for different Months and it should work.

See Attached.

[Sintek]

@ tumanovr...
Please explain requirement by uploading sample file showing what needs to happen when previous data has been posted and when new data is entered...
Perhaps you should have been more transparent in post 1...

[tumanovr]

As you can see from the Excel file attached when you run the VBA the first time it finds the products with the same name and what is the amount and puts as it should first order
in section order 1 and the second in Order 2 (they are marked green) but when new data is insert and
you run the vba again it just copies again everything from the last free cell so it becomes a double posting (highlighted in red)
I need that when you run the vba it posts the data and when you insert new data the system check what is already there
and just puts new data in the table

[Sintek]

system check what is already there
and just puts new data in the table

Checks how? and puts new data where?
Still no transparency...you just ran the same data again...imagine new data was added...then show what code is supposed to do???
Mind reader...I am not...still no answer to post 6...

FYI ...No way a formula solution can solve this...

[tumanovr]

So

I have sheet Main where I have my Product list With Month, Date Item and Amount. Then I have 2 other sheets March and April, in those two sheets I have specific products which I need to know how many orders where made.

So if lets say if in the Main sheet I have a product 'Adapter USB' 2 times I need that the VBA code finds this product in the Main sheet and takes the ordered amount (only) and puts it Amount number in the April or March sheet depending on the Month (now the main sheet has only products for April). Also when the Amount is transferred to sheet April (or other month) the VBA puts the amount in the right cells. The first Adapter USB with the amount ordered is 2 so this amount 2 goes to the April sheet in cell Order 1 the second Adapter USB with the amount 2 goes to the April sheet Order 2. Before you suggested to use the VBA code:

Sub J3v16()
Dim Data, Rw, Col As Long, i As Long
Data = Sheets("Main").Cells(1).CurrentRegion
For i = 2 To UBound(Data)
If Evaluate("ISREF('" & "" & Data(i, 1) & "" & "'!A1)") = True Then
With Sheets("" & Data(i, 1) & "")
Rw = Application.Match(Data(i, 3), .Range("A:A"), 0)
If Not IsError(Rw) Then
Col = .Cells(Rw, .Columns.Count).End(xlToLeft).Column + 1
.Cells(Rw, Col) = Data(i, 4)
End If
End With
End If
Next i
End Sub

This code does what it should do it finds the products from the Main sheet and puts the Amounts in the right cells Order1 and Order 2 where is the right product in the April sheet.

But if lets say I put in the Main sheet a new line with the with the same product Adapter USB so now I have in the Main sheet not 2 products Adapter USB but 3 so in the April sheet when I start the VBA the Amounts from the Main sheet should go 1 Adapter USB to April sheet Adapter USB In cell Order 1, the second Adapter USB to Order 2 and the third to Order 3. At the moment the VBA keeps the Amounts which where already transferred to April sheet Order 1 and 2 and just starts from the beginning but from the cell Order 3 and inserts the old amounts whit the new one. So in the end I have for one product in sheet April 5 orders but in the Main sheet is only 3.

I need now that the VBA code also looks if in sheet April there is already something transferred like the first 2 Products Adapter USB and they are in April sheet in cells Order 1 and 2 and just adds to cell Order 3 the new Amount which I have added in the the sheet Main.

[Gregb11]

Did you try my formula in Post #5? Was there an issue?

[Sintek]

So if you are going to be updating main all the time then the previously entered will have to be cleared and re-populated again...
This should solve...

Please Login or Register  to view this content.