Back

Technologies:

javascriptjavascript
avatar
Tolerim
24 days ago

How can I write a script to meet the criteria when pasting into Excel?

Although my experience with complicated scripts for Excel is limited to basic tasks, I would like to learn how to implement a more complex script. Specifically, I am interested in a script that pastes rows of data into sheet 2 with conditional criteria. The basic idea of what I want to achieve is demonstrated in the attached document. Essentially, I aim to transfer data from sheet 1 to sheet 2 only when certain criteria are met, namely when both columns B and D contain valid data. This results in a count of 10.

It would be great if I could transfer entire rows of data to sheet 2, starting in column C.

If anyone could offer guidance on how to accomplish this, it would be extremely appreciated. If needed, I can provide the template document for your reference. Due to my lack of experience in this area, I have not attempted this task and would require support.

Answers(2)
avatar
Vogi
23 days ago
Verified Answer
I can definitely help you with that. Here's how you can transfer data from sheet 1 to sheet 2 only when certain criteria are met: 1. Open the Excel file and go to the sheet where you want to copy the data. 2. Click on the first cell where you want to paste the data. 3. Go to the "Data" tab and click on "From Other Sources". 4. Select "From Microsoft Query" and click "OK". 5. Select the Excel file that contains the data you want to copy and click "OK". 6. Select the sheet that contains the data you want to copy and click "OK". 7. In the Query Wizard, select the columns that contain the data you want to copy. 8. Click on "Criteria" and enter your criteria in the appropriate fields. 9. Click "OK" to close the Criteria dialog box. 10. Click "Next" to proceed with the query. 11. Choose how you want to view the data (Table or PivotTable) and click "Finish". 12. Save your query by clicking on "File" > "Save As" and selecting a location for your query. You can also use VBA code to accomplish this task. Here's an example of how you can transfer entire rows of data from sheet 1 to sheet 2, starting in column C: 
Sub CopyRows()
Dim i As Long
Dim j As Long
Dim lastrow As Long

lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
    If Sheets("Sheet1").Cells(i, 2) <> "" And Sheets("Sheet1").Cells(i, 4) <> "" Then
        j = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row + 1
        Sheets("Sheet1").Rows(i).Copy Destination:=Sheets("Sheet2").Range("C" & j)
    End If
Next i
End Sub
This code will copy entire rows of data from Sheet1 to Sheet2 only when both columns B and D contain valid data. I hope this helps! Let me know if you have any questions or if there's anything else I can help with.

Sources

How to Copy Conditional Formatting in Microsoft Excelexcel - Copy and paste to sheet 2 based on column A conditional in ...excel - Copy a Row of Data based on a conditional 'Date' value from ...Tips for using copy and paste | Smartsheet Learning Centermicrosoft excel - How to Copy a row conditionally to another sheet ...
avatar
Tolerim
24 days ago
Unfortunately, this does not seem to be a question related to JavaScript or JavaScript libraries. It appears to be a question about writing a script for Microsoft Excel. I recommend seeking help from resources that specialize in Excel scripting such as the Excel Developer Community on Microsoft's website or seeking help from a professional Excel developer. Good luck!
;