Copy data from one worksheet into another

lstithem

New Member
Joined
Feb 10, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet with multiple worksheets. I would like it to automatically copy information from one sheet to another based on a criteria. The main tab is called "Quoted Work". I have active columns A-D. I set up a column "F" to use as my identifier. If the data entered info Column "F" = Yes then I want it to copy the entire row (not "F") into an existing worksheet named "AwardedJobs". This row should be placed into the next available line as it already contains all of the jobs we are currently working on. I have been researching but just can't seem to get the coding to work correctly. Can you help! Thank you in advance.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Welcom to MrExcel board. Check below code:

VBA Code:
Sub copyData()
    
    Dim sourceRows As Integer, destRows As Integer
    sourceRows = Sheets("Quoted Work").Cells(Rows.Count, 1).End(xlUp).Row
    destRows = Sheets("AwardedJobs").Cells(Rows.Count, 1).End(xlUp).Row
    
    For rowno = 2 To sourceRows
        If Sheets("Quoted Work").Range("F" & rowno) = "Yes" Then
            Sheets("Quoted Work").Range("A" & rowno & ":D" & rowno).Copy Destination:=Sheets("AwardedJobs").Range("A" & destRows + 1)
            destRows = destRows + 1
        End If
    Next
End Sub
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Another method (without loop)

VBA Code:
    Dim destRows As Integer

    destRows = Sheets("AwardedJobs").Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim rang As Range
    Set sh = Worksheets("Quoted Work")

        sh.Range("$F$1:$F$11").AutoFilter Field:=1, Criteria1:="Yes"
        Set rang = sh.UsedRange.Offset(1, 0)
        Set rang = rang.Resize(rang.Rows.Count - 1, rang.Columns.Count - 2)

        On Error Resume Next
        Set rang = rang.SpecialCells(xlCellTypeVisible)
        
        If Err.Number = 0 Then
            rang.Copy
            Sheets("AwardedJobs").Select
            Range("A" & destRows + 1).Select
            ActiveSheet.Paste
        End If

        On Error GoTo 0

        sh.Cells.AutoFilter

        Application.CutCopyMode = False
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Another option
VBA Code:
Sub Istithem()
   With Sheets("Quoted Work")
      .Range("A1:F1").AutoFilter 6, "Yes"
      .AutoFilter.Range.Offset(1).Columns("A:D").Copy Sheets("AwardedJobs").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .AutoFilterMode = False
   End With
End Sub
 

Thom78

New Member
Joined
Mar 30, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Hi everyone,

I am new to this site and would be greatly appreciated for any assistance.

I have a spreadsheet that I wish to transfer the rows that meet the value of “on hold” or “completed” from In Flight Updates worksheet to On Hold worksheet and Completed worksheet

thanks in advance
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Please start a thread of your own for this question. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

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