Copy data from one worksheet into another

lstithem

New Member
Joined
Feb 10, 2021
Messages
2
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Please start a thread of your own for this question. Thanks
 
Upvote 0
Thank you for your response. After reworking my spreadsheet I realized that I didn't give all the information needed to make this code work. I will try one more time to explain what I am working with. I can also upload my file if that would help. I have been researching and trying different codes for months and just can't get it to work. Again, any help is greatly appreciated!

In my spreadsheet I will be working with four tabs which are also tables: Quoted Work, Awarded Jobs, Job Sheet & Temp Fence. On the First tab "Quoted Work" there is a field called "Awarded" Column "E". If there is an "X" in this column, I need that specific row to copy to to the "Awarded Job" tab but only the information in columns A, B & C and then also copied to the "Job Sheet" tab but only the information in B & C columns for that particular row. They will be placed into columns A & B on that sheet. (I hope that made since). Lastly, on the "Awarded Jobs" tab there is a field called "Temp Fence" Column "D". If there is a "Yes" in this column, I need that specific row to copy to the "Temp Fence" tab but only the information in columns B & C and placed in columns A & B as above. I also should mention that when these copy to new tabs, I will want them to be inserted into the next available row. I do have some conditional formatting and Data validations in my tables. I am not sure if that will effect anything. Can all of this be done?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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
Back
Top