VBA to find text in a column and copy a range of adjacent cells in the same row to another sheet

[JKK22]

Hello,
I am a beginner at Macros.
I want to find the text "T+1" in column C , and in that same row , copy values of cells in columns I-L to another spreadsheet .
So if it finds "T+1" in cell C20, i want to copy the values of cells I20:L20 into another workbook.
I found a way to do it one by one, but I have to believe there is a way to do it using a range.

Here is what i have that works, but I am wondering if there is a better way. I'd appreciate any suggestions.
Thank You

Sub Get Data
Dim nPath As String
Dim fileNameString As String
Dim fileName As String
nPath = "\\Swib\root\ShrInvOper\Fund Accounting\Reporting\InvestOne\"
fileNameString = Format(Date, "dd-mmm-yyyy") & " 1 SIF Holding Detail by Maturity Date" & ".xls"
fileName = Dir(nPath & fileNameString)

Workbooks.Open nPath & fileName
Sheets("Main").Select
Dim a As Long
For a = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(a, "C").Value = "T+1" Then
Cells(a, "I").Copy
ThisWorkbook.Activate
ActiveSheet.Select
Range("D7").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End If
Next
Workbooks.Open nPath & fileName
Sheets("Main").Select
Dim b As Long
For b = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(b, "C").Value = "T+1" Then
Cells(b, "J").Copy
ThisWorkbook.Activate
ActiveSheet.Select
Range("E7").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next
Workbooks.Open nPath & fileName
Sheets("Main").Select
Dim c As Long
For c = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(c, "C").Value = "T+1" Then
Cells(c, "K").Copy
ThisWorkbook.Activate
ActiveSheet.Select
Range("F7").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next
Workbooks.Open nPath & fileName
Sheets("Main").Select
Dim d As Long
For d = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(d, "C").Value = "T+1" Then
Cells(d, "L").Copy
ThisWorkbook.Activate
ActiveSheet.Select
Range("G7").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next
Range("D7").Select
Application.CutCopyMode = False
Workbooks(fileName).Close

End Sub

 

[kevin9999]

Could you provide a sample of the "Main" sheet using the XL2BB add in so we have some actual data to work with?

 

[JKK22]

Could you provide a sample of the "Main" sheet using the XL2BB add in so we have some actual data to work with?

Could you provide a sample of the "Main" sheet using the XL2BB add in so we have some actual data to work with?

My Pc is not letting me use the add-in. Is there another way ?

 

[JKK22]

Could you provide a sample of the "Main" sheet using the XL2BB add in so we have some actual data to work with?

Main.SS2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
2		AS OF 10/12/2022
3
4
5
6
7
8		DM	MD	SN	Desc	TLSH	TLSHPR	YM	TLAC	TLC	TLA	TLAA
9		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
10		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
11		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
12		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
13		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
14		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
15		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
16		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
17		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
18		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
19		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
20		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
21		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
22		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.09	10.00	10.00	10.00	10.00
23		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.08	10.00	10.00	10.00	10.00
24		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.05	10.00	10.00	10.00	10.00
25		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.07	10.00	10.00	10.00	10.00
26		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.07	10.00	10.00	10.00	10.00
27		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.03	10.00	10.00	10.00	10.00
28		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.00	10.00	10.00	10.00	10.00
29		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	2.98	10.00	10.00	10.00	10.00
30		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.05	10.00	10.00	10.00	10.00
31		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.05	10.00	10.00	10.00	10.00
32		1	10/13/2022	xxxxxx	xxxxxxxxxxxxxxxx	10.00	0.0%	3.05	10.00	10.00	10.00	10.00
33		1	T+1			240.00	0.0%	3.07	240.00	240.00	240.00	240.00
34
35		1	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	3.34	5.00	5.00	5.00	5.00
36		1	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	3.13	5.00	5.00	5.00	5.00
37		1	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	1.88	5.00	5.00	5.00	5.00
38		1	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	1.88	5.00	5.00	5.00	5.00
39		1	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	1.87	5.00	5.00	5.00	5.00
40		1	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	1.87	5.00	5.00	5.00	5.00
41		2	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	3.04	5.00	5.00	5.00	5.00
42		2	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	2.57	5.00	5.00	5.00	5.00
43		2	10/14/2022	xxxxxx	xxxxxxxxxxxxxxxx	5.00	0.0%	2.57	5.00	5.00	5.00	5.00
44		1	T+2			45.00	0.0%	2.46	45.00	45.00	45.00	45.00
45
Sheet1

Cell Formulas
Range	Formula
F33,I33:L33	F33	=SUM(F9:F32)
F44,I44:L44	F44	=SUM(F35:F43)

 

[kevin9999]

Couple of things: try to avoid using ActiveSheet.Select, if you run the code from the wrong sheet things will go wrong. Along the same lines, be very specific with your sheet names – you’ll avoid much pain in the future.

I can’t fully test your code because I don’t have your folder setup to open the file you’re after, but the code below does what I think you’re looking for. Just change the destination sheet to its proper name.

Option Explicit
Sub Get_Data()
    
    Dim nPath As String
    Dim fileNameString As String
    Dim fileName As String
    nPath = "\\Swib\root\ShrInvOper\Fund Accounting\Reporting\InvestOne\"
    fileNameString = Format(Date, "dd-mmm-yyyy") & " 1 SIF Holding Detail by Maturity Date" & ".xls"
    fileName = Dir(nPath & fileNameString)
    Workbooks.Open nPath & fileName
    
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb.Worksheets("Main")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2") '<<< change this to your actual sheet name

    Dim LRow As Long
    LRow = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
    
    With ws1.Range("B8:L" & LRow)
        .AutoFilter 2, "T+1"
        .Offset(1, 7).Resize(.Rows.Count - 1, 4).Copy
        ws2.Range("D7").PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = 0
        .AutoFilter
    End With

End Sub

 

[JKK22]

Couple of things: try to avoid using ActiveSheet.Select, if you run the code from the wrong sheet things will go wrong. Along the same lines, be very specific with your sheet names – you’ll avoid much pain in the future.

I can’t fully test your code because I don’t have your folder setup to open the file you’re after, but the code below does what I think you’re looking for. Just change the destination sheet to its proper name.

Option Explicit
Sub Get_Data()
   
    Dim nPath As String
    Dim fileNameString As String
    Dim fileName As String
    nPath = "\\Swib\root\ShrInvOper\Fund Accounting\Reporting\InvestOne\"
    fileNameString = Format(Date, "dd-mmm-yyyy") & " 1 SIF Holding Detail by Maturity Date" & ".xls"
    fileName = Dir(nPath & fileNameString)
    Workbooks.Open nPath & fileName
   
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = wb.Worksheets("Main")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2") '<<< change this to your actual sheet name

    Dim LRow As Long
    LRow = ws1.Cells.Find("*", , xlFormulas, , 1, 2).Row
   
    With ws1.Range("B8:L" & LRow)
        .AutoFilter 2, "T+1"
        .Offset(1, 7).Resize(.Rows.Count - 1, 4).Copy
        ws2.Range("D7").PasteSpecial xlPasteValuesAndNumberFormats
        Application.CutCopyMode = 0
        .AutoFilter
    End With

End Sub

Thank you for the tips-
I use ActiveSheet because the tab names change each day. Tabs are named to reference dates. (ex: 10.12.22, 10.11.22, 10.10.22.) How would you code this to open the right tab?

 

[kevin9999]

To be clear, is "Main" the 'source' data sheet and ActiveSheet the 'destination' sheet?

 

[JKK22]

Thank you for the tips-
I use ActiveSheet because the tab names change each day. Tabs are named to reference dates. (ex: 10.12.22, 10.11.22, 10.10.22.) How would you code this to open the right tab?

The code you provided worked well- thank you!. Now the only question left is how to code it to reference a tab named as a date ( previous reply) .

 

[kevin9999]

The code you provided worked well

How did you get the code to 'work well'. In other words, what method did you use to select the correct sheet (tab)? If it changes every day, then you might as well use ActiveSheet - and I'll rejig the code once you answer:

To be clear, is "Main" the 'source' data sheet and ActiveSheet the 'destination' sheet?

 

[JKK22]

How did you get the code to 'work well'. In other words, what method did you use to select the correct sheet (tab)? If it changes every day, then you might as well use ActiveSheet - and I'll rejig the code once you answer:

I used ActiveSheet, and yes, "Main" the 'source' data sheet and ActiveSheet the 'destination' sheet?