Appreciate any suggestion

0 votes

If the source and destination dates match and I use the following formula, I can copy and paste certain cells from a worksheet called "Jobs" with a raw data table to a worksheet called "Schedule." But it only copies one of the three jobs I have scheduled for that day. I would appreciate any assistance.

Sub CopyBasedonSheet1()

  Dim i As Long
  Dim j As Long
  
  Worksheets("Schedule").Range("B1:AJ92").ClearContents
  
  Sheet1LastRow = Worksheets("Jobs").Range("G" & Rows.Count).End(xlUp).Row 'G is the Date Column'
  Sheet2LastRow = Worksheets("Schedule").Range("A" & Rows.Count).End(xlUp).Row 'A is the Date column'
  
  For j = 1 To Sheet1LastRow
    For i = 1 To Sheet2LastRow
            
      If Worksheets("Jobs").Cells(j, 7).Value = Worksheets("Schedule").Cells(i, 1).Value And Worksheets("Jobs").Cells(j, 1).Value = "P" Then
        Worksheets("Schedule").Cells(i, 2).Value = Worksheets("Jobs").Cells(j, 3).Value 
        Worksheets("Schedule").Cells(i, 3).Value = Worksheets("Jobs").Cells(j, 9).Value 
        Worksheets("Schedule").Cells(i, 4).Value = Worksheets("Jobs").Cells(j, 14).Value 
      End If
    Next i
  Next j

End Sub
Feb 14, 2023 in Others by narikkadan
• 63,720 points
443 views

1 answer to this question.

0 votes

You could try and add a feature to concatenate multiple jobs:

 If Not IsEmpty(Worksheets("Schedule").Cells(i, 4).Value) Then Worksheets("Schedule").Cells(i, 4).Value = Worksheets("Schedule").Cells(i, 4).Value & ", " & Worksheets("Jobs").Cells(j, 14).Value.

 Make sure to clear the data before starting, otherwise, it will duplicate the jobs and/or display old data..

answered Feb 14, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

why linux is more secure than any other operating system?

First thing i would like to tell ...READ MORE

answered Jul 12, 2018 in Others by kristena1234
• 160 points
2,290 views
0 votes
1 answer

How to include/ install any library in node.js application program?

Node.js is server side runtime environment that ...READ MORE

answered Jul 26, 2019 in Others by ArchanaNagur
• 2,360 points
1,369 views
0 votes
1 answer

anyone can give suggestion for video conferencing

Hello Prakash Motiani I would be suggesting you ...READ MORE

answered May 9, 2020 in Others by Anadya

edited May 13, 2020 by Gitika 921 views
0 votes
1 answer

Row's Children Must Not Contain Any Null Values Flutter Error.

Hi@akhtar, You get this error because in your ...READ MORE

answered Sep 15, 2020 in Others by MD
• 95,440 points
5,180 views
0 votes
1 answer

Do We get the certificate if we attend both the days of the workshop and not do any assignments ?

Hi, @There, Yes, you can, if you have attended ...READ MORE

answered Sep 21, 2020 in Others by Gitika
• 65,910 points
2,121 views
0 votes
1 answer

Is there any way to change input type="date" format?

To answer your question, it is impossible ...READ MORE

answered Feb 18, 2022 in Others by Aditya
• 7,680 points
14,656 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
995 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,350 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
662 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
875 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP