LinkBack URL
About LinkBacks
Copy value from one cell to another if defined conditions are satisfied
Hi,
Please help me to create a excel vba code:
i am using example for explanation purpose:
1. Consider an Excel workbook with multiple sheets. In the "VBASheet" sheet, we have data like this:
VBASheet:
| A | B | C | D | E | F | G |
|-------|-------|-------------|--------|-------|-------|-------|
| 1 | KW 15 | A1 SB-27 | A1 | | | |
| 2 | KW15 | A3 SB-38 | A3 SB | | | |
| 3 | KW15 | A3 SE-38 | A3 Lim | | | |
| | | | | | | |
In "VBASheet" sheet, go through Column D. If a sheet name is found in Column D, we'll retrieve values from Columns B and C in that row. (if "A1" Sheet is found in workbook then retrieve values from Column B and C in that row, here value in Cell B="KW 15" and value in Cell C="A1 SB-27")
Before running VBA Code:
Sheet "A1":
| A | B | C | D | E | F I
|-------|-------|-------|-------|-------|
| Data1 | A1 SB-27 | KW15| KW16 | Jan | KW17 |
| Data4 | Value2 | Sheet1| Data5 | Dat6 | 11 I
| Data7 | Value3 | Sheet2| Data8 | Dat9 | 12 I
| Data1 | Value1 | Sheet3| Data2 | Dat3 | 13 I
| Data4 | Value2 | Sheet4| Da5 | Daa6 | 14 I
| Data7 | Value3 | Sheet1| Data8 | Dat9 | 15 I
Next, we'll search for the value in Cell C in the corresponding sheet (so we look for "A1 SB-27" in Sheet "A1"). If "A1 SB-27" is found in Cell B2 in Sheet "A1" then in the same row look for "KW15" which was cell value in Column B in "VBASheet" Sheet. When both condition is fulfilled then offset by (1,0) -> to give cell D2 in this example and check if this cell includes "KW" as value in it. Here when we offset by (1,0) to "KW15" in "A1" Sheet than cell value is "KW16", which includes "KW" in it. then offset by (1,0) to "KW16" (CEll D2) and copy value of this cell D2 ("Sheet1") in cell which is located (3,0) to it. So copy "Sheet1" and replace "Sheet4" by "Sheet1", continue this loop by moving (1,0) to cell D2 now which is Cell E2 ("Jan") and check if cell E2 includes "KW" if yes then below above copy operation and if not then again move (1,0) to cell E2...
After running VBA Code:
Sheet "A1":
| A | B | C | D | E | F I
|-------|-------|-------|-------|-------|
| Data1 | A1 SB-27 | KW15 | KW16 I Jan | KW17 |
| Data4 | Value2 | Sheet1 | Data5 | Dat6 | 11 I
| Data7 | Value3 | Sheet2 | Data8 | Dat9 | 12 I
| Data1 | Value1 | Sheet3 | Data2 | Dat3 | 13 I
| Data4 | Value2 | Sheet4 | Data5 | Daa6 | 11 I
| Data7 | Value3 | Sheet1 | Data8 | Dat9 | 15 I
In my "VBASheet" Sheet, there are more than 50 sheets listed in column D as i have this 50 worksheet also in my workbook and this similar logic must work for all the sheets
Register To Reply
Originally Posted by Mumps1
Bookmarks