VBA- Search String, Copy info from multi columns and paste

[luc_carolino]

Hello! This is my first post here and I'm brand new with VBA stuff. I'm trying to learn it. I tried to build the code but no success until now.

I have a spreadsheet with 2 worksheets.
On Sheet1, I have general information and I'm using column B to V.
On Sheet2, I have a column with cells that represent the month name (january, february and so on...), plus 3 columns that I need to fill with information that will come from sheet1.

On sheet1:
column B is called Priority, column C is Date (mon/yy. Example: sep/22)

What I'd like to do:
search for the string "Critical" on column B.
Identify the month in Column C.
and then I'd like to copy the value from cells F, H and J to sheet2, on the right row accordingly to month.

Not sure if I were clear enough but I appreciate any tips to accomplish this.
Thank you very much!

 

[luc_carolino]

I started to create this code to test some things.
I'm not checking the month/year and also I'm not moving to the next line on sheet2.

Sub Processing()

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 1 To Lastrow
If Cells(i, 2).Value = "Critical" Then
Cells(i, "F").Copy Destination:=Worksheets("Sheet2").Range("C5")
Cells(i, "H").Copy Destination:=Worksheets("Sheet2").Range("D5")
Cells(i, "J").Copy Destination:=Worksheets("Sheet2").Range("E5")

End If

Next
Application.ScreenUpdating = True
End Sub

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Rhodie72]

I started to create this code to test some things.
I'm not checking the month/year and also I'm not moving to the next line on sheet2.

Sub Processing()

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 1 To Lastrow
If Cells(i, 2).Value = "Critical" Then
Cells(i, "F").Copy Destination:=Worksheets("Sheet2").Range("C5")
Cells(i, "H").Copy Destination:=Worksheets("Sheet2").Range("D5")
Cells(i, "J").Copy Destination:=Worksheets("Sheet2").Range("E5")

End If

Next
Application.ScreenUpdating = True
End Sub

Sub Processing()

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

    For i = 1 To Lastrow
        If Cells(i, 2).Value = "Critical" Then
            Cells(i, "F").Copy Destination:=Worksheets("Sheet2").Range("C5")
            Cells(i, "H").Copy Destination:=Worksheets("Sheet2").Range("D5")
            Cells(i, "J").Copy Destination:=Worksheets("Sheet2").Range("E5")
          
        End If
              
    Next
Application.ScreenUpdating = True
End Sub

YOU FORGOT "Next i"

 

[luc_carolino]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Sheet1:

control.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
3	Priority	Planned Date	GenInfo	GenInfo	Target 1	GenInfo	Target 2	GenInfo	Target 3	GenInfo	GenInfo	GenInfo
4	regular		x	x	Text Info 1	x	Information ABC 123	x	Example Info	x	x	x
5	Critical	set/22	x	x	Text Info 1	x	Example	x	Example Info	x	x	x
6	high		x	x	Text Info 4	x	Information ABC 123	x	Just another test	x	x	x
7	High		x	x	Text Info 4	x	Information ABC 123	x	Just another test	x	x	x
8	Alta		x	x	Text Info 2	x	Example	x	Just another test	x	x	x
9	Critical	set/22	x	x	Text Info 2	x	Example	x	VBA Saves	x	x	x
10	Critical	out/22	x	x	Text Info 25	x	Information ABC 123	x	Example Info	x	x	x
11	Low		x	x	Text Info 3	x	Information ABC 123	x	VBA Saves	x	x	x
12	Low		x	x	Text Info 2	x	Test Info	x	Studying Info	x	x	x
13	High		x	x	Text Info 10	x	Generic Info	x	Example Info	x	x	x
14	Low		x	x	Text Info 8	x	Generic Info	x	Studying Info	x	x	x
Sheet1

Sheet2:

control.xlsm
	B	C	D	E	F	G	H	I	J
2	2022					2023
3
4	September	Info From: Column F	Info from Column H	info from Column J		January	Column F	Column H	Column J
5		Text Info 1	Example	Example Info
6		Text Info 2	Example	VBA Saves
7
8
9	October	Info From: Column F	Info from Column H	info from Column J		February	Column F	Column H	Column J
10		Text Info 25	Information ABC 123	Example Info
11
12
13
14	November	Info From: Column F	Info from Column H	info from Column J		March	Column F	Column H	Column J
15
16
17
18
19	December	Info From: Column F	Info from Column H	info from Column J		April	Column F	Column H	Column J
20
21
22
23
24						May	Column F	Column H	Column J
25
26
27
Sheet2

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[luc_carolino]

What I'm trying to achieve:

From sheet 1, I need to identify on column B, the cells with the text "Critical".
Then, I need to check on column C, the month and year.
Next step, is to copy the information from column F, H and J to sheet2.

I've post the mini sheet with some examples.

 

[luc_carolino]

I'm writing the following code.
From the initial tests, it's working, but still need a lot of development and improvement.


Sub ProcessaPlanejado()

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim getMonth As String
Dim getYear As String

Lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 1 To Lastrow
If Cells(i, 2).Value = "Critical" Then
getMonth = Month(Cells(i, 3))
getYear = Year(Cells(i, 3))

Select Case getYear
Case "2022": MsgBox "Test 2022"
Select Case getMonth
Case 1:
Case 2:
Case 3:
Case 4:
Case 5:
Case 6:
Case 7:
Case 8:
Case 9: 'september
If IsEmpty(Worksheets("Sheet2").Range("C5")) Then
Cells(i, "F").Copy Destination:=Worksheets("Sheet2").Range("C5")
Cells(i, "H").Copy Destination:=Worksheets("Sheet2").Range("D5")
Cells(i, "J").Copy Destination:=Worksheets("Sheet2").Range("E5")
Else
Cells(i, "F").Copy Destination:=Worksheets("Sheet2").Range("C6")
Cells(i, "H").Copy Destination:=Worksheets("Sheet2").Range("D6")
Cells(i, "J").Copy Destination:=Worksheets("Sheet2").Range("E6")
End If
Case 10:
Case 11:
Case 12:
End Select

'Case "2023": Need to build"

End Select


End If

Next i
Application.ScreenUpdating = True
End Sub

 

[mumps]

I can see a few problems with how the data on Sheet2 is organized. You have the months and years in merged cells. You should avoid using merged cell because they almost always create problems for macros. Also, for each month, you have room for only four rows of data. What happens if you have five critical dates in column C of Sheet1 for any month? I would suggest that you re-design Sheet2 to remove all merged cells and then post a screen shot of the revised sheet.

 

[luc_carolino]

I can see a few problems with how the data on Sheet2 is organized. You have the months and years in merged cells. You should avoid using merged cell because they almost always create problems for macros. Also, for each month, you have room for only four rows of data. What happens if you have five critical dates in column C of Sheet1 for any month? I would suggest that you re-design Sheet2 to remove all merged cells and then post a screen shot of the revised sheet.

Thanks for the tip of merged cells x macro, I didn´t know about that.
I designed that layout because at first I didn´t think about a more complex automation/intelligence (for example: a macro that search for the correct row to place the datas accordingly the month/year (that´s why a put a Case with fixed range in the past code and also due to my limited skills :D )

About the limited rows, I did that way because I can have a maximum of 3 criticals per month (I'm thinking in writing a code to check this rule within the column and alert with a msgbox in case that more than 3 dates detected).

 

[mumps]

If you plan on re-designing Sheet2 to remove all merged cells, then please post a screen shot of the revised sheet.