VBA script to copy specific rows based on column contents

plaasma30

New Member
Joined
Mar 13, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello fellow excel users,

I have 5 spreadsheets each with 48 columns. Based on the values entered in 'column B' I would like to copy those particular rows but not all the columns to a separate 'destination' sheet. Also, checking across the rest of the spreadsheets. Please see below to the corresponding mini-sheet versions for your reference. There are three different sheets, namely Sheet1, Sheet2 and Sheet 3 each with 20 columns. The values are entered in column B of every sheet. Some values in some of the columns (such as columns J, K and M) rely on the values from other columns. I have highlighted the normal columns (in yellow), the columns with formula (in green) and rows (in red). The ones highlighted in red are the ones to be copied to their corresponding columns (marked as bold) that are found in the destination sheet . I am not sure how to handle the cells with formula while copying them to the destination sheet. Any help would be kindly appreciated.

contents of Sheet1:
sample_sheet.xlsx
ABCDEFGHIJKLMNOPQRST
9data1data2data3data4data5data6data7data8data9data10data11data12data13data14data15data16data17data18data19data20
10
11gjdlkgd1dgdgd3434sdfdsf5454fsdf464565445679981009126546488884564fgfddfgdf5fdg3434fg
12dfglkdfgdgdfg435sfds454dsfds6544654348895308546889544dfgdfgdf45fdg34345dfg
13dfglkdfg2dfgkldf434fds454sdfsd545469107600548885654dfgdfgdf45fdg3453dfg
14dfglkfddfgdd343sdfdsf454sdfds4555464017100154797564dfgdfgdf45dfg3453dfg
15fdlgnkdkfldfgdf343sdfdsf456fsdfsdf54456401975104567995654dfgdfgdf3fdg34543dfg
16dfklgndkfdfgdfg53sdfsd45664sdf45456390950145645717456dfgdfg2fgdf3453dfg
Sheet1
Cell Formulas
RangeFormula
J11:J16J11=N11+S11
K11:K16K11=H11+I11
M11:M16M11=D11+F11


contents of Sheet2:
sample_sheet.xlsx
ABCDEFGHIJKLMNOPQRST
8data1data2data3data4data5data6data7data8data9data10data11data12data13data14data15data16data17data18data19data20
9
10gjdlkgddgdgd3434sdfdsf5454fsdf464565445679981009126546488884564fgfddfgdf5fdg3434fg
11dfglkdfg3dgdfg435sfds454dsfds6544654348895308546889544dfgdfgdf45fdg34345dfg
12dfglkdfgdfgkldf434fds454sdfsd545469107600548885654dfgdfgdf45fdg3453dfg
13dfglkfddfgdd343sdfdsf454sdfds4555464017100154797564dfgdfgdf45dfg3453dfg
14fdlgnkdkfldfgdf343sdfdsf456fsdfsdf54456401975104567995654dfgdfgdf3fdg34543dfg
15dfklgndkf5dfgdfg53sdfsd45664sdf45456390950145645717456dfgdfg2fgdf3453dfg
Sheet2
Cell Formulas
RangeFormula
J10:J15J10=N10+S10
K10:K15K10=H10+I10
M10:M15M10=D10+F10


contents of Sheet3:
sample_sheet.xlsx
ABCDEFGHIJKLMNOPQRST
9data1data2data3data4data5data6data7data8data9data10data11data12data13data14data15data16data17data18data19data20
10
11gjdlkgd2dgdgd3434sdfdsf5454fsdf464565445679981009126546488884564fgfddfgdf5fdg3434fg
12dfglkdfgdgdfg435sfds454dsfds6544654348895308546889544dfgdfgdf45fdg34345dfg
13dfglkdfgdfgkldf434fds454sdfsd545469107600548885654dfgdfgdf45fdg3453dfg
14dfglkfddfgdd343sdfdsf454sdfds4555464017100154797564dfgdfgdf45dfg3453dfg
15fdlgnkdkfl4dfgdf343sdfdsf456fsdfsdf54456401975104567995654dfgdfgdf3fdg34543dfg
16dfklgndkfdfgdfg53sdfsd45664sdf45456390950145645717456dfgdfg2fgdf3453dfg
Sheet3
Cell Formulas
RangeFormula
J11:J16J11=N11+S11
K11:K16K11=H11+I11
M11:M16M11=D11+F11


contents of destination:
sample_sheet.xlsx
ABCDEF
5data1data2data4data10data11data13
destination


Thanks,
plasma30
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Hi and welcome to the board.
One question for information that's needed to write a code:
What's the name of the sheet that's not mentioned out of the 5 sheets?
 

plaasma30

New Member
Joined
Mar 13, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi thank you for your kind reply .
The sample file that I supplied is for illustrative purpose only. The original file contains 5 sheets which I can't share for privacy reasons.
The sample file only contains four sheets, namely Sheet 1, Sheet 2, Sheet 3 and destination.
Sheets 1-3 contain the specific rows (highlighted in red) that I would like to copy to the sheet titled "destination", please.
I will then apply the code from here to the original file accordingly.
Please do let me know if I missed something.
Thanks,
Plasma30
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Try this in a standard module:
VBA Code:
Sub OutputValueToSheetDestination()
    Dim heading As Range, data2 As Range, data2Temp As Range, lr As Long
    
    Application.ScreenUpdating = False
    For Each Sheet In Sheets
        With Sheet
            If .Name <> "destination" Then 'If you have other sheets irrelevant to data ouput, their names need to be specified here
                Set heading = .Range("B:B").Find("data2")
                Set data2 = .Range("B:B").Find("*", .Range(heading.Address(0, 0)))
                Set data2Temp = data2
                Do While Not data2 Is Nothing
                    If data2.Row <= heading.Row Then Exit Do
                    lr = Sheets("destination").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Sheets("destination").Cells(lr, "A").Value = .Cells(data2.Row, "A").Value
                    Sheets("destination").Cells(lr, "B").Value = .Cells(data2.Row, "B").Value
                    Sheets("destination").Cells(lr, "C").Value = .Cells(data2.Row, "D").Value
                    Sheets("destination").Cells(lr, "D").Value = "=" & .Name & "!" & .Cells(data2.Row, "J").Address
                    Sheets("destination").Cells(lr, "E").Value = "=" & .Name & "!" & .Cells(data2.Row, "K").Address
                    Sheets("destination").Cells(lr, "F").Value = "=" & .Name & "!" & .Cells(data2.Row, "M").Address
                    Set data2 = .Range("B:B").FindNext(data2)
                    If data2.Address = data2Temp.Address Then Exit Do
                Loop
            End If
        End With
    Next Sheet
    Application.ScreenUpdating = True
    
End Sub
 

plaasma30

New Member
Joined
Mar 13, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

This works great!! Thank you.
I get you question now. I have 5 sheets in the original file out of 50 sheets that are named differently but I can add a matching initial say 'W_' to recognize the 5 sheets in question. But how can proceed. I can use the following piece of code before `With Sheet` loop but I am getting an error:
`If Left$(Sheet.Name, 2) = "W_" Then`
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
Glad it worked and thanks for feedback.
I am getting an error
Replace the line with my comment with:
VBA Code:
If Left(.Name, 2) = "W_" Then
(You just have the redundant "$".)
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

And I stuck to outputting formulas for data10, 11, and 13 but actually the following code does fundamentally the same trick:
VBA Code:
Sub OutputValueToSheetDestination()
    Dim heading As Range, data2 As Range, data2Temp As Range, lr As Long
    
    Application.ScreenUpdating = False
    For Each Sheet In Sheets
        With Sheet
            'If .Name <> "destination" Then
            If Left(.Name, 2) = "W_" Then 'Sheets' names for data ouput need to be specified here
                Set heading = .Range("B:B").Find("data2")
                Set data2 = .Range("B:B").Find("*", .Range(heading.Address(0, 0)))
                Set data2Temp = data2
                Do While Not data2 Is Nothing
                    If data2.Row <= heading.Row Then Exit Do
                    lr = Sheets("destination").Cells(Rows.Count, "A").End(xlUp).Row + 1
                    Sheets("destination").Cells(lr, "A").Value = .Cells(data2.Row, "A").Value
                    Sheets("destination").Cells(lr, "B").Value = .Cells(data2.Row, "B").Value
                    Sheets("destination").Cells(lr, "C").Value = .Cells(data2.Row, "D").Value
                    Sheets("destination").Cells(lr, "D").Value = .Cells(data2.Row, "J").Value 'Changed
                    Sheets("destination").Cells(lr, "E").Value = .Cells(data2.Row, "K").Value 'Changed
                    Sheets("destination").Cells(lr, "F").Value = .Cells(data2.Row, "M").Value 'Changed
                    Set data2 = .Range("B:B").FindNext(data2)
                    If data2.Address = data2Temp.Address Then Exit Do
                Loop
            End If
        End With
    Next Sheet
    Application.ScreenUpdating = True
    
End Sub
The code in #4 outputs formulas like so:
1164844 Copy specific values to another ws.xlsm
ABCDEF
1data1data2data4data10data11data13
2gjdlkgd1343479981009128888
destination
Cell Formulas
RangeFormula
D2D2=Sheet1!$J$11
E2E2=Sheet1!$K$11
F2F2=Sheet1!$M$11

But the one in this post doesn't (although both result in the same values)
1164844 Copy specific values to another ws.xlsm
ABCDEF
1data1data2data4data10data11data13
2gjdlkgd1343479981009128888
destination
 

plaasma30

New Member
Joined
Mar 13, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Perfect and they both work great for me. Thank you.
I have two more questions, please
How can I get the output to start from cell C16?
How can I make the output from one of the columns with formula to exactly show as percentage value instead of showing in decimal?
 

kanadaaa

Active Member
Joined
Dec 29, 2019
Messages
348
Office Version
  1. 365
Platform
  1. Windows
How can I get the output to start from cell C16?
Assuming you have headers like so:
1164844 Copy specific values to another ws.xlsm
CDEFGH
15data1data2data4data10data11data13
16
destination

Change these lines:
Rich (BB code):
lr = Sheets("destination").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("destination").Cells(lr, "A").Value = .Cells(data2.Row, "A").Value
Sheets("destination").Cells(lr, "B").Value = .Cells(data2.Row, "B").Value
Sheets("destination").Cells(lr, "C").Value = .Cells(data2.Row, "D").Value
Sheets("destination").Cells(lr, "D").Value = .Cells(data2.Row, "J").Value
Sheets("destination").Cells(lr, "E").Value = .Cells(data2.Row, "K").Value
Sheets("destination").Cells(lr, "F").Value = .Cells(data2.Row, "M").Value
Like below (make sure that you save the workbook before running the macro):
VBA Code:
Sub OutputValueToSheetDestination()
    Dim heading As Range, data2 As Range, lr As Long
   
    Application.ScreenUpdating = False
    For Each Sheet In Sheets
        With Sheet
            'If .Name <> "destination" Then
            If Left(.Name, 2) = "W_" Then 'Sheets' names for data ouput need to be specified here
                Set heading = .Range("B:B").Find("data2")
                Set data2 = .Range("B:B").Find("*", .Range(heading.Address(0, 0)))
                Do While Not data2 Is Nothing
                    If data2.Row <= heading.Row Then Exit Do
                    lr = Sheets("destination").Cells(Rows.Count, "C").End(xlUp).Row + 1 'Changed
                    Sheets("destination").Cells(lr, "C").Value = .Cells(data2.Row, "A").Value 'Changed
                    Sheets("destination").Cells(lr, "D").Value = .Cells(data2.Row, "B").Value 'Changed
                    Sheets("destination").Cells(lr, "E").Value = .Cells(data2.Row, "D").Value 'Changed
                    Sheets("destination").Cells(lr, "F").Value = .Cells(data2.Row, "J").Value 'Changed
                    Sheets("destination").Cells(lr, "G").Value = .Cells(data2.Row, "K").Value 'Changed
                    Sheets("destination").Cells(lr, "H").Value = .Cells(data2.Row, "M").Value 'Changed
                    Set data2 = .Range("B:B").FindNext(data2)
                Loop
            End If
        End With
    Next Sheet
    Application.ScreenUpdating = True
   
End Sub
Note:
If there are nonempty cells below C16, we'll need a little more modifications.
How can I make the output from one of the columns with formula to exactly show as percentage value instead of showing in decimal?
You don't need a code to achieve that (although you CAN achieve it with a code).

Capture.PNG


Result:

Capture2.PNG
 
Last edited:

plaasma30

New Member
Joined
Mar 13, 2021
Messages
6
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thanks for your post. It's of great help!!
My headers are a bit different than the ones I posted in my sample file.
There are a few lines of info before the header then a gap of an entire row so that the output starts from C17 onwards. Hope this makes sense.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,047
Messages
5,639,767
Members
417,110
Latest member
RayClarke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top