Copy row with specific values from multiple sheets to 2 different sheets that may contain that specific value

mniceguy81

New Member
Joined
May 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Wishing you all well, I'm new to the world of Excel and VBA but maybe you can help me, I'm trying to learn how to copy data from multiple sheets in my Excel file called Check.xlsx but not copying anything from the first sheet which is called "IN" but from the others that are "Tst1", "Tst2", "Tst3", "Tst4" , "Tst5"

Data is found in Column C named "Status" and the rows have values like "Mini Fridge" or "Big Fridge" or "Small Fridge" and i need them to be copied to Sheet called "Mini Fridge" or "Big Fridge" or "Small Fridge" with the same values.

Could i ask kindly for your help.

Thank you very much.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
You have a workbook called Check.xlsx. It has sheets called "IN", "Tst1", "Tst2", "Tst3", "Tst4" , "Tst5".
In column C (starting from row 2?) of those Tst sheets, you have data like "Mini Fridge", "Big Fridge", "Small Fridge".
You want to copy these data to sheets named "Mini Fridge", "Big Fridge", "Small Fridge" accordingly.

It is not clear where you want to copy them to. Another workbook with sheets named "Mini Fridge", "Big Fridge", "Small Fridge"?
 

mniceguy81

New Member
Joined
May 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you for your reply, the data should be sent to another sheet called sheet named "Mini Fridge", "Big Fridge", "Small Fridge"?, on the same workbook = Check.xlsx

thank you
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
This code is not tested but I believe you could understand how it works
VBA Code:
Sub CopyFridge()

Dim rowNext As Long
Dim cell As Range, rngData As Range
Dim ws As Worksheet
Dim wsMF As Worksheet, wsBF As Worksheet, wsSF As Worksheet

' Define variable name for worksheet
Set wsMF = ActiveWorkbook.Sheets("Mini Fridge")
Set wsBF = ActiveWorkbook.Sheets("Big Fridge")
Set wsSF = ActiveWorkbook.Sheets("Small Fridge")

For Each ws In Sheets
    Select Case ws.Name
        Case "IN", "Mini Fridge", "Big Fridge", "Small Fridge"
            ' Do nothing
        Case Else                   ' Provided there is no sheets other than Tst
            ' Define data range in Tst sheet, assuming data is on column A (A2 and below)
            Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A"))
            ' Loop through all data in ws
            For Each cell In rngData
                If cell = "Mini Fridge" Then
                    ' Find next empty copy destination row in column A
                    rowNext = wsMF.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    cell.Copy wsMF.Range("A" & rowNext)
                ElseIf cell = "Big Fridge" Then
                    ' Find next empty copy destination row in column A
                    rowNext = wsBF.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    cell.Copy wsBF.Range("A" & rowNext)
                ElseIf cell = "Small Fridge" Then
                    ' Find next empty copy destination row in column A
                    rowNext = wsSF.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    cell.Copy wsSF.Range("A" & rowNext)
                End If
            Next
    End Select
End Sub
 

mniceguy81

New Member
Joined
May 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This code is not tested but I believe you could understand how it works
VBA Code:
Sub CopyFridge()

Dim rowNext As Long
Dim cell As Range, rngData As Range
Dim ws As Worksheet
Dim wsMF As Worksheet, wsBF As Worksheet, wsSF As Worksheet

' Define variable name for worksheet
Set wsMF = ActiveWorkbook.Sheets("Mini Fridge")
Set wsBF = ActiveWorkbook.Sheets("Big Fridge")
Set wsSF = ActiveWorkbook.Sheets("Small Fridge")

For Each ws In Sheets
    Select Case ws.Name
        Case "IN", "Mini Fridge", "Big Fridge", "Small Fridge"
            ' Do nothing
        Case Else                   ' Provided there is no sheets other than Tst
            ' Define data range in Tst sheet, assuming data is on column A (A2 and below)
            Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "A"))
            ' Loop through all data in ws
            For Each cell In rngData
                If cell = "Mini Fridge" Then
                    ' Find next empty copy destination row in column A
                    rowNext = wsMF.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    cell.Copy wsMF.Range("A" & rowNext)
                ElseIf cell = "Big Fridge" Then
                    ' Find next empty copy destination row in column A
                    rowNext = wsBF.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    cell.Copy wsBF.Range("A" & rowNext)
                ElseIf cell = "Small Fridge" Then
                    ' Find next empty copy destination row in column A
                    rowNext = wsSF.Cells(Rows.Count, "A").End(xlUp).Row + 1
                    cell.Copy wsSF.Range("A" & rowNext)
                End If
            Next
    End Select
End Sub
Thank you very much and will test it and give you a feedback
 

mniceguy81

New Member
Joined
May 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Dear Zot,

Thank you for the VBA code but lost a little bit,

I have 2 Columns, one is as you provided me in the code which is in column A and content starts from A2, how can I add the second column?.

Thank you for your help
 

mniceguy81

New Member
Joined
May 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Dear Zot,

Thank you for the VBA code but lost a little bit,

I have 2 Columns, one is as you provided me in the code which is in column A and content starts from A2, how can I add the second column?.

Thank you for your help
Sorry forgot to say the other column is B and starts from B2
 

mniceguy81

New Member
Joined
May 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Dear Zot,

Sorry for confusing you, here a sample and will like to explain myself properly and would kindly ask you to forgive me as i was not clear and gave wrong info.

I have Check.xlsx file, it contains the following sheets = "In", "Tst1", "Tst2", "Tst3", "Tst4" , "Tst5" & "Dt (data) from Tst1 to Tst5".

Data i want will come only from A & B (Sorry before i said C but was writing like you were in front of me).

I would like to check in sheets Tst1 to Tst5 (ignoring sheet "In") and copy data from them to "Dt from Tst1 to Tst5" with the same info and structure of the columns.

Hope it is clear and sorry for wasting your time, also thank you for taking the time to help
 

Attachments

  • Contents of Tst1.jpg
    Contents of Tst1.jpg
    49.2 KB · Views: 3
  • Contents of DT from Tst1 to Tst5.jpg
    Contents of DT from Tst1 to Tst5.jpg
    49.7 KB · Views: 3
  • Contents of In.jpg
    Contents of In.jpg
    46.5 KB · Views: 2

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,410
Office Version
  1. 2016
Platform
  1. Windows
This will put all data in each Tst sheets into sheet Dt (data) from Tst1 to Tst5.
VBA Code:
Sub CopyFridge()

Dim rowNext As Long
Dim rngData As Range
Dim ws As Worksheet, wsSummary As Worksheet

' Define variable name for worksheet
Set wsSummary = ActiveWorkbook.Sheets("Dt (data) from Tst1 to Tst5")

For Each ws In Sheets
    If Left(ws.Name, 3) = "Tst" Then
        ' Define data range in Tst sheet, assuming data is on column A (A2 and below)
        Set rngData = ws.Range("A2", ws.Cells(Rows.Count, "C"))
        ' Find next empty copy destination row in column A
        rowNext = wsSummary.Cells(Rows.Count, "A").End(xlUp).Row + 1
        rngData.Copy wsSummary.Range("A" & rowNext).PasteSpecial(xlPasteValues)
    End If
Next ws

End Sub
 

Forum statistics

Threads
1,136,290
Messages
5,674,893
Members
419,532
Latest member
longphamtel

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