VBA Loop to paste a different value into blank cells on different worksheet

[smanni3]

I have two worksheets in the same workbook, Sheet 1 which has % by date and Sheet 2 which I overwrite each month with the new %.
Sheet 1 Looks like this, I want to copy each instance of Location A and paste it into the next blank cell in Sheet 2. So that I copy Container 1 - Location A and paste into D13 which is blank and then copy Container 2 - Location A and paste it into the next BLANK cell in Sheet 2 which is D24 and corresponds to Nov-21 of Container 2. I want to repeat this until the last cell in the Range on Sheet 2 because I don't want to continue the process for EVERY blank just the Blanks in the current populated range. Any help with this is greatly appreciated!

 

[DanteAmor]

Hi and welcome to MrExcel.

Unfortunately in your images the letters of the columns are not visible.
So I'm going to assume that the Container column on sheet1 is in column B.
The % column of sheet2 is in column D.

Note: So that this does not happen, you can use the XL2BB tool minisheet, which allows you to paste information from your sheet, for example:

Dante Amor
	A	B	C	D
1		Container	Location	Percent
2		1	A	45%
3		1	B	68%
4		1	C	28%
5		1	D	14%
6		1	E	88%
7		1	F	91%
8		2	A	69%
9		2	B	16%
10		2	C	71%
11		2	D	85%
12		2	E	15%
13		2	F	46%
14		3	A	79%
15		3	B	85%
16		3	C	15%
17		3	D	46%
18		3	E	73%
19		3	F	35%
20		4	A	36%
21		4	B	17%
22		4	C	58%
23		4	D	85%
24		4	E	46%
25		4	F	14%
26		5	A	78%
Sheet1

Try this:

Sub LoopValues()
  Dim c As Range, f As Range
  
  For Each c In Sheets("Sheet2").Range("D2:D" & Sheets("Sheet2").Range("C" & Rows.count).End(3).Row).SpecialCells(xlCellTypeBlanks).Areas
    Set f = Sheets("Sheet1").Range("B:B").Find(c.Offset(, -2).Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then c.Value = f.Offset(, 2).Value
  Next
End Sub

 

[smanni3]

Sheet 1 has my Percent Met by Category, Measure and Location. I want to copy the Percent Met for Location A ONLY and paste it into Sheet 2 for each Measure.
For example, I want to copy E3 from Sheet 1 and paste into the first blank cell in Sheet 2 (D13). It has to look for the Blank Cell because I already have code that creates that blank cell and it changes each month. So my second one would be to copy E9 where Category, Measure are same in both sheets and Location = A. Paste that value in Sheet 2 for same Category and Measure in cell D26. Continue to Copy Cell E15 from Sheet 1 where Category and Measure match sheet 2 then paste in cell D38. Can someone help me? This seems to be beyond my expertise. I need to repeat this process until there are no more blanks in Sheet 2 and no more Location A in Sheet 1.

 

[smanni3]

Hi and welcome to MrExcel.

Unfortunately in your images the letters of the columns are not visible.
So I'm going to assume that the Container column on sheet1 is in column B.
The % column of sheet2 is in column D.

Note: So that this does not happen, you can use the XL2BB tool minisheet, which allows you to paste information from your sheet, for example:

Dante Amor
	A	B	C	D
1		Container	Location	Percent
2		1	A	45%
3		1	B	68%
4		1	C	28%
5		1	D	14%
6		1	E	88%
7		1	F	91%
8		2	A	69%
9		2	B	16%
10		2	C	71%
11		2	D	85%
12		2	E	15%
13		2	F	46%
14		3	A	79%
15		3	B	85%
16		3	C	15%
17		3	D	46%
18		3	E	73%
19		3	F	35%
20		4	A	36%
21		4	B	17%
22		4	C	58%
23		4	D	85%
24		4	E	46%
25		4	F	14%
26		5	A	78%
Sheet1

Try this:

Sub LoopValues()
  Dim c As Range, f As Range
 
  For Each c In Sheets("Sheet2").Range("D2:D" & Sheets("Sheet2").Range("C" & Rows.count).End(3).Row).SpecialCells(xlCellTypeBlanks).Areas
    Set f = Sheets("Sheet1").Range("B:B").Find(c.Offset(, -2).Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then c.Value = f.Offset(, 2).Value
  Next
End Sub

Sheet 1 has my Percent Met by Category, Measure and Location. I want to copy the Percent Met for Location A ONLY and paste it into Sheet 2 for each Measure.
For example, I want to copy E3 from Sheet 1 and paste into the first blank cell in Sheet 2 (D13). It has to look for the Blank Cell because I already have code that creates that blank cell and it changes each month. So my second one would be to copy E9 where Category, Measure are same in both sheets and Location = A. Paste that value in Sheet 2 for same Category and Measure in cell D26. Continue to Copy Cell E15 from Sheet 1 where Category and Measure match sheet 2 then paste in cell D38. Can someone help me? This seems to be beyond my expertise. I need to repeat this process until there are no more blanks in Sheet 2 and no more Location A in Sheet 1.