ASadStudent
New Member
- Joined
- Oct 26, 2022
- Messages
- 20
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I got helped before with a macro that copies amounts from 1 excel file to another excel file based on the names that are in the B column. It also makes the B column red if it didn't find anything to copy it to.
Now I need to add a couple more things to this macro so I wanted to ask if anyone could help me with this.
This is what the original macro does:
My macro copies data from 1 excel document sheet to another. The document where the data is coming from is called “report” and the document where the data needs to go to is called “maandverband”. The sheets in both documents are just called Sheet1.
The data is copied based on the product name that is in the B Column of both documents. If the product names on both files match then it needs to copy the amount that is in the N column in the “report” document to the F Column in the “Maandverband” document.
This is what I want to add:
What I want to add to this code is that there are multiple places where it can copy the data to. Now there are 12 columns where the amounts need to go to and it needs to copy the amount to the correct location based on file name. For example you have 12 columns, amount january, amount february etc until you get amount december.
The "report" file where the amounts come from also has different names based on month of the year. So for example report january, report february etc until report december.
What I want the code to do is that when it sees that the file name is "report february" that it puts the amounts found in the map in the correct column
For example the file name is "report january" so it copies the amount in the N column of "report january" and pastes it into the F column of the file "Maandverband".
Here is what my code looks like right now:
And here is what the excel file sheets look like:
Thanks a bunch for helping me solve my problem.
I got helped before with a macro that copies amounts from 1 excel file to another excel file based on the names that are in the B column. It also makes the B column red if it didn't find anything to copy it to.
Now I need to add a couple more things to this macro so I wanted to ask if anyone could help me with this.
This is what the original macro does:
My macro copies data from 1 excel document sheet to another. The document where the data is coming from is called “report” and the document where the data needs to go to is called “maandverband”. The sheets in both documents are just called Sheet1.
The data is copied based on the product name that is in the B Column of both documents. If the product names on both files match then it needs to copy the amount that is in the N column in the “report” document to the F Column in the “Maandverband” document.
This is what I want to add:
What I want to add to this code is that there are multiple places where it can copy the data to. Now there are 12 columns where the amounts need to go to and it needs to copy the amount to the correct location based on file name. For example you have 12 columns, amount january, amount february etc until you get amount december.
The "report" file where the amounts come from also has different names based on month of the year. So for example report january, report february etc until report december.
What I want the code to do is that when it sees that the file name is "report february" that it puts the amounts found in the map in the correct column
For example the file name is "report january" so it copies the amount in the N column of "report january" and pastes it into the F column of the file "Maandverband".
Here is what my code looks like right now:
VBA Code:
Sub CopyPasteCode()
Dim report As Worksheet, Maandverband As Worksheet
Dim data As Variant, ky As Variant
Dim lr As Long, rw As Long
Dim d As Object, d2 As Object
Dim rng As Range
Set d = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
Set report = Workbooks.Item("Report").Sheets("Sheet1")
Set Maandverband = Workbooks.Item("Maandverband").Sheets(1)
lr = report.Cells(Rows.Count, 2).End(3).Row
With report.Cells(1, 1).Resize(lr, 14)
data = .Value
.Interior.ColorIndex = xlNone
End With
For rw = LBound(data) To UBound(data)
If data(rw, 14) <> 0 Then
ky = data(rw, 2)
If Not d.exists(ky) Then
d(ky) = data(rw, 14) & "|" & rw
End If
End If
Next rw
lr = Maandverband.Cells(Rows.Count, 2).End(3).Row
data = Maandverband.Cells(1, 1).Resize(lr, 6).Formula
For rw = LBound(data) To UBound(data)
ky = data(rw, 2)
d2(ky) = Empty
If d.exists(ky) Then
data(rw, 6) = Split(d(ky), "|")(0)
End If
Next rw
For Each ky In d.keys
If Not d2.exists(ky) Then
rw = Split(d(ky), "|")(1)
If rng Is Nothing Then
Set rng = report.Cells(rw, 2)
Else
Set rng = Union(rng, report.Cells(rw, 2))
End If
End If
Next
If Not rng Is Nothing Then rng.Interior.Color = vbRed
Maandverband.Cells(1, 6).Resize(UBound(data)).Formula = Application.Index(data, 0, 6)
End Sub
And here is what the excel file sheets look like:
Report January.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | Product name | Amount sold | |||||||||||||||
2 | Product 1 | 50 | |||||||||||||||
3 | Product 2 | 165 | |||||||||||||||
4 | Product 3 | 163 | |||||||||||||||
5 | Product 4 | 643 | |||||||||||||||
6 | Product 5 | 34 | |||||||||||||||
7 | Product 6 | 2 | |||||||||||||||
8 | Product 7 | 75 | |||||||||||||||
9 | Product 8 | 93 | |||||||||||||||
10 | Product 9 | 66 | |||||||||||||||
11 | Product 10 | 85 | |||||||||||||||
12 | Product 11 | 45 | |||||||||||||||
13 | Product 12 | 78 | |||||||||||||||
14 | Product 13 | 35 | |||||||||||||||
15 | Product 14 | 29 | |||||||||||||||
16 | Product 15 | 61 | |||||||||||||||
17 | Product 16 | 144 | |||||||||||||||
18 | Product 17 | 72 | |||||||||||||||
19 | Product 18 | 0 | |||||||||||||||
20 | Product 19 | 0 | |||||||||||||||
21 | Product 20 | 5 | |||||||||||||||
22 | |||||||||||||||||
23 | |||||||||||||||||
Sheet1 |
Maandverband.xlsx | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | ProductName | Amount in January | Amount in February | Amount in March | Amount in April | Amount in May | Amount in June | Amount in July | Amount in August | Amount in September | Amount in October | Amount in November | Amount in December | |||||||
2 | Product 1 | |||||||||||||||||||
3 | Product 2 | |||||||||||||||||||
4 | Product 3 | |||||||||||||||||||
5 | Product 4 | |||||||||||||||||||
6 | Product 5 | |||||||||||||||||||
7 | Product 6 | |||||||||||||||||||
8 | Product 7 | |||||||||||||||||||
9 | Product 8 | |||||||||||||||||||
10 | Product 9 | |||||||||||||||||||
11 | Product 10 | |||||||||||||||||||
12 | Product 11 | |||||||||||||||||||
13 | Product 12 | |||||||||||||||||||
14 | Product 13 | |||||||||||||||||||
15 | Product 14 | |||||||||||||||||||
16 | Product 15 | |||||||||||||||||||
17 | Product 16 | |||||||||||||||||||
18 | Product 17 | |||||||||||||||||||
19 | Product 18 | |||||||||||||||||||
20 | Product 19 | |||||||||||||||||||
21 | Product 20 | |||||||||||||||||||
22 | ||||||||||||||||||||
Sheet1 |
Thanks a bunch for helping me solve my problem.
Last edited: