Page 2 of 3 FirstFirst 123 LastLast
Results 41 to 80 of 86

Thread: Copying data from different sheet to sheet 1 on Excel

  1. #41

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    I am now getting a type mismatch error on this part
    Code:
     vrow.Offset(nxtrw).Value = Round(rw.Offset(oset).Value, 2)
    Here is the full code for the section
    Code:
    For Each ws In ActiveWorkbook.Sheets   ' if the code is in some other workbook to the worksheets, change the workbook object to suit
      f = p & Left(ws.Name, 4) & ".xlsx"
      If Len(Dir(f)) > 0 Then
        With Workbooks.Open(f)
          ws.Copy , .Sheets(1)
    
            Set dept = .Sheets(1)
            Set dt = .Sheets(2)
            Set vrow = dept.Range("a:a").Find("GRAND TOTAL").Offset(4)
            vrow.Font.Bold = True
            vrow.Value = "Variation"
            dept.Range("b12:d13").Copy vrow.Offset(1, 1)
            vrow.Offset(2, 2).Value = "Contracted"
            Set vrow = vrow.Offset(4)
            Set rw = dept.Cells(17, 1)
            oset = 0
            nxtrw = 0
            Do
                If IsEmpty(rw.Offset(oset)) Then Exit Do
                If InStr(rw.Offset(oset), "Temp") = 0 And InStr(rw.Offset(oset), "Agency") = 0 Then
                    Set fnd = dt.Range("c:c").Find(Left(rw.Offset(oset), 4))
                    If Not fnd Is Nothing Then
                        Set fndval = fnd.End(xlDown).Offset(-1, 7)
                        If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(Rows.Count - 21, 7).End(xlUp)
                        vrow.Offset(nxtrw).Value = Round(rw.Offset(oset).Value, 2)
                        vrow.Offset(nxtrw, 1).Value = Round(rw.Offset(oset, 1).Value, 2)
                       ' vrow.Offset(nxtrw).Resize(, 2).Value = rw.Offset(oset).Resize(, 2).Value
                       ' vrow.Offset(nxtrw, 2).Value = fndval.Value * -1
                        rwcnt = vrow.Row + nxtrw
                        'vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")*-1"
                        vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")"
                        nxtrw = nxtrw + 1
                    End If
                
                 vrow.Offset(nxtrow + 1, 1).Formula = "=sum(" & vrow.Offset(, 1).Address(False, False) & ":" & vrow.Offset(nxtrow - 1, 1).Address(False, False) & ")"
                    With vrow.Offset(nxtrow + 1, 1).Resize(, 3)
                        .Formula = vrow.Offset(nxtrow + 1, 1).Formula
                        .Borders(3).LineStyle = 1
                        .Borders(3).ColorIndex = -4105
                        .Borders(4).LineStyle = -4119
                        .Borders(4).Weight = 4
                        .Borders(4).ColorIndex = -4105
                    End With
                    vrow.Offset(-4).Resize(nxtrow + 1, 4).Interior.Color = 12566463
                End If
                oset = oset + 1
            Loop
          .Sheets(1).Select
          .Close True
        End With
        Else
         errlist = errlist & "No valid reports for " & ws.Name & vbNewLine
      End If
    Next
    
    MsgBox errlist
    
    End Sub

  2. #42
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    ok, i had time now to test the code and fix the problems, mostly just wrong offsets, and some in wrong order
    Code:
            Set dept = .Sheets(1)
            Set dt = .Sheets(2)
            Set vrow = dept.Range("a:a").Find("GRAND TOTAL").Offset(4)
            vrow.Value = "Variation"
            dept.Range("b12:d13").Copy vrow.Offset(1, 1)
            vrow.Offset(2, 2).Value = "Contracted"
            Set vrow = vrow.Offset(4)
            Set rw = dept.Cells(17, 1)
            oset = 0
            nxtrw = 0
            Do
                If IsEmpty(rw.Offset(oset)) Then Exit Do
                If InStr(rw.Offset(oset), "Temp") = 0 And InStr(rw.Offset(oset), "Agency") = 0 Then
                    Set fnd = dt.Range("c:c").Find(Left(rw.Offset(oset), 4))
                    If Not fnd Is Nothing Then
                        Set fndval = fnd.End(xlDown).Offset(-1, 7)
                        If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(Rows.Count - 21, 7).End(xlUp)
                        vrow.Offset(nxtrw).Value = rw.Offset(oset).Value
                        vrow.Offset(nxtrw, 1).Value = Round(rw.Offset(oset, 1).Value, 2)
                        
                        vrow.Offset(nxtrw, 2).Value = fndval.Value
                        rwcnt = vrow.Row + nxtrw
                        vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")*-1"
                        nxtrw = nxtrw + 1
                    End If
                End If
                oset = oset + 1
            Loop
            vrow.Offset(nxtrw + 1, 1).Formula = "=sum(" & vrow.Offset(, 1).Resize(nxtrw).Address(False, False) & ")"
            With vrow.Offset(nxtrw + 1, 1).Resize(, 3)
                .Formula = vrow.Offset(nxtrw + 1, 1).Formula
                .Borders(3).LineStyle = 1
                .Borders(3).ColorIndex = -4105
                .Borders(4).LineStyle = -4119
                .Borders(4).Weight = 4
                .Borders(4).ColorIndex = -4105
            End With
            vrow.Offset(-4).Resize(nxtrw + 6, 4).Interior.Color = 12566463
         .Sheets(1).Select
          .Close True
        End With
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #43

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Thanks so much Pete. Really appreciate your help. It works for a couple of reports but then I get a debug "Application-defined or object-defined error" on this part of the code
    Code:
     Set fndval = fnd.Offset(Rows.Count - 21, 7).End(xlUp)
    which is part of this string
    Code:
     If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(Rows.Count - 21, 7).End(xlUp)
    Also on the sum line can I add the value "Total" in column A?

    Thanks so much.

  4. #44
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    Also on the sum line can I add the value "Total" in column A?
    add the bottom line
    Code:
            vrow.Offset(nxtrw + 1, 1).Formula = "=sum(" & vrow.Offset(, 1).Resize(nxtrw).Address(False, False) & ")"
            vrow.offset(nxtrw +1).value = "Total"

    It works for a couple of reports but then I get a debug "Application-defined or object-defined error" on this part of the code
    does it always happen on the same workbooks? or after the same number of workbooks?
    i only keep testing the code against the one workbook, try changing as below
    Code:
    If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(dt.Rows.Count - 21, 7).End(xlUp)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #45

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Thanks Pete. I still get the error on the same part of the code. Can you please tell me what this part of the code is doing so I can understand maybe that will help me try and find the issue.
    Code:
    Set fndval = fnd.Offset(dt.Rows.Count - 21, 7).End(xlUp)
    I did notice that on some reports there were multiple lines with the same code but had extra sub analysis codes so i just want to pick up the first line with the code and ignore the lines which have the extra sub analysis code that don't have a sub analysis code of 9999. To give an example

    1234 Grade 2 9999
    1234 Grade 2 AB12
    1234 Grade 2 66YY

  6. #46

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Ok Pete managed to get it to work. I changed the count to - 100. Random number I picked and it worked!

    Code:
    If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(dt.Rows.Count - 100, 7).End(xlUp)
    I need to understand what the count is doing.

    One other thing I have noticed whilst trying to resolve the above issue is that there are some lines which have a budget amount but not one for the Contracted amount so there isn't anything on sheet 2 for this code and as the code is being looked up on sheet 1 and 2 it is not picking up this code on the Variation part.
    If there is a budget amount on a code on sheet 1 then I need to show this on the Variation section as this in effect is a variation.
    Is this possible? Thanks

  7. #47
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    Is this possible?
    probably

    I need to understand what the count is doing.
    you expect me to remember that far back?

    it was to do with if there was no data in the column C below the looked up value, meaning the number of rows of data for the specific item wold not be found, resulting in fndval.row being the last row of the worksheet, this would always only occur for the last item on the dt sheet
    21 was the last item row on the sample sheet, something i was probably going to fix later, but forgot
    try like
    Code:
                        If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(dt.Rows.Count - fnd.Row, 7).End(xlUp)
    I changed the count to - 100. Random number I picked and it worked!
    in real terms you could use any number that would less then the number of rows in the worksheet but greater than the maximum possible data rows
    Code:
    If fndval.Row = dt.Rows.Count - 1 Then Set fndval = dt.cells(999, 10).End(xlUp)
    where 999 is just a random number greater than the possible number of used rows in that column
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #48

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Thanks Pete.
    You have helped me so much with this and I really appreciate it.
    Can you please help me with this last bit where there is a budget amount on sheet but not a match on sheet 2 it should still add that line to the Variation section showing the budget amount and 0 for the contracted amount.
    Also to pick up just the first row on sheet 1 which matches the code on sheet 2 and to ignore the additional rows with the same code.
    Thanks so much.

  9. #49

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Thanks Pete.
    You have helped me so much with this and I really appreciate it.
    Can you please help me with this last bit where there is a budget amount on sheet but not a match on sheet 2 it should still add that line to the Variation section showing the budget amount and 0 for the contracted amount.
    Also to pick up just the first row on sheet 1 which matches the code on sheet 2 and to ignore the additional rows with the same code.
    Thanks so much.

  10. #50
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    not enough information for either request
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #51

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Quote Originally Posted by westconn1 View Post
    not enough information for either request
    Hi Pete...on the first sheet where the variation section is being added there are some codes which also have a sub code against it so there could be multiple same codes but with different sub codes so in order not over complicate things I just want to pick up the first line that the code matches otherwise what it happening all the rows with the codes and sub codes are being copied down to the variation section and from the second sheet the contracted column figures are being added to all of these lines which then makes the variation data incorrect. Here is an example to illustrate the multiple codes

    1234 Grade 2 9999
    1234 Grade 2 AB12
    1234 Grade 2 66YY

    So I just want to pick up the first line 1234 Grade 2 9999.

    The second part is that on the first sheet there could be codes which have a budget amount but there not be any contracted amounts for that code on the second sheet so in these cases the code with the budget amount is not being added to the variation section as the lookup does not find this code on the second sheet.
    But I need to pick up the codes where there are budget amounts but not for the contracted amounts as this will also be a variation.

    I hope this makes sense. Thanks

  12. #52

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Hi Pete...please help with this if you can. Thanks

  13. #53
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    i still do not understand the requirement
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #54

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    OK I am attaching an example workbook.
    On the first sheet 4444 there are 3 rows with the same initial code 1234 and then three different sub codes. I want to only pick up the first row 1234 Grade 2 9999 otherwise the information will get duplicated.

    1234 Grade 2 9999
    1234 Grade 2 AB12
    1234 Grade 2 66YY

    The second part of my requirement is on the first sheet there is a code 2222 Grade 7 which has a budget value but there isn't a contracted figure on the 4444DT sheet but as there is a budget amount I need to show it on the variation section.

    I hope this makes sense. Thanks
    Attached Files Attached Files

  15. #55

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Hi Pete...have you had a chance to check the example file I attached to explain what I need the code to do? Thanks.

  16. #56

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Quote Originally Posted by fusion001 View Post
    OK I am attaching an example workbook.
    On the first sheet 4444 there are 3 rows with the same initial code 1234 and then three different sub codes. I want to only pick up the first row 1234 Grade 2 9999 otherwise the information will get duplicated.

    1234 Grade 2 9999
    1234 Grade 2 AB12
    1234 Grade 2 66YY

    The second part of my requirement is on the first sheet there is a code 2222 Grade 7 which has a budget value but there isn't a contracted figure on the 4444DT sheet but as there is a budget amount I need to show it on the variation section.

    I hope this makes sense. Thanks
    Hi Pete

    Does this example help explain what I need to do? Thanks

  17. #57
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    could 1234 have different grades?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #58

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    1234 could have multiple sub codes like

    1234 Grade 2 9999
    1234 Grade 2 AB12
    1234 Grade 2 66YY

    so for the look up i only want to pick up the first line with 1234, in this case 1234 Grade 2 9999. The rest of the lines for 1234 can be ignored. For all the different grades the first line will always have a sub code of 9999 and then may be some other sub codes.

    Also if the line has a budget value but nothing for the contracted then I still need to show on the variation section as there is a budget value.

    Thanks for your help.

  19. #59
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    you can try this, i am not sure it has enough checks, but seems to do the basics requested for the sample data supplied, plenty of tests required

    Code:
            Set dept = .Sheets(1)
            Set dt = .Sheets(2)
            Set vrow = dept.Range("a:a").Find("GRAND TOTAL").Offset(4)
            vrow.Value = "Variation"
            dept.Range("b12:d13").Copy vrow.Offset(1, 1)
            vrow.Offset(2, 2).Value = "Contracted"
            Set vrow = vrow.Offset(4)
            Set rw = dept.Cells(17, 1)
            oset = 0
            nxtrw = 0
            Do
                If IsEmpty(rw.Offset(oset)) Then Exit Do
                If InStr(rw.Offset(oset), "Temp") = 0 And InStr(rw.Offset(oset), "Agency") = 0 And Not rw.Offset(oset, 1) = 0 Then
                    Set fnd = dt.Range("c:c").Find(Left(rw.Offset(oset), 4))
                    If Not fnd Is Nothing Then
                        Set fndval = fnd.End(xlDown).Offset(-1, 7)
                        If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(Rows.Count - 21, 7).End(xlUp)
                        vrow.Offset(nxtrw).Value = rw.Offset(oset).Value
                        vrow.Offset(nxtrw, 1).Value = Round(rw.Offset(oset, 1).Value, 2)
                        
                        vrow.Offset(nxtrw, 2).Value = fndval.Value
                        rwcnt = vrow.Row + nxtrw
                        vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")*-1"
                        nxtrw = nxtrw + 1
                        Else
                        If rw.Offset(oset, 2) = 0 Then vrow.Offset(nxtrw, 1).Resize(, 3).Value = rw.Offset(oset, 1).Resize(, 3): nxtrw = nxtrw + 1
                    End If
                End If
                oset = oset + 1
            Loop
            vrow.Offset(nxtrw + 1, 1).Formula = "=sum(" & vrow.Offset(, 1).Resize(nxtrw).Address(False, False) & ")"
            With vrow.Offset(nxtrw + 1, 1).Resize(, 3)
                .Formula = vrow.Offset(nxtrw + 1, 1).Formula
                .Borders(3).LineStyle = 1
                .Borders(3).ColorIndex = -4105
                .Borders(4).LineStyle = -4119
                .Borders(4).Weight = 4
                .Borders(4).ColorIndex = -4105
            End With
            vrow.Offset(-4).Resize(nxtrw + 6, 4).Interior.Color = 12566463
         .Sheets(1).Select
          .Close True
        End With
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  20. #60

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Hi Pete...the code runs without any errors but doesn't do what's required.

    Where there is a value in the A1 Budget column for a code on the first sheet but no value in the A1 contracted column in the second sheet it is not adding it to the variation part.
    If there is a budget figure but no contracted figure then I still need to show it on the variation part.

    Thanks so much for your help.

  21. #61
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    try changing the line as below
    Code:
                        If rw.Offset(oset, 2) = 0 Then vrow.Offset(nxtrw).Resize(, 4).Value = rw.Offset(oset).Resize(, 4).Value: nxtrw = nxtrw + 1
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  22. #62

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    That's perfect. Thanks so much. I'll try it out with multiple reports.

    Thanks so much for your help Pete.

  23. #63

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Hi Pete...i checked a report where is no budget figure on the first sheet but there is a contracted figure on the second sheet. This didn't add to the variation section.
    So we could have instances where there may be a budget figure but no contracted figure and vice versa.
    Thanks

  24. #64

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Quote Originally Posted by fusion001 View Post
    Hi Pete...i checked a report where is no budget figure on the first sheet but there is a contracted figure on the second sheet. This didn't add to the variation section.
    So we could have instances where there may be a budget figure but no contracted figure and vice versa.
    Thanks
    Hi Pete...have you had a chance to check this please?

  25. #65

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Hi Pete I'd really appreciate your help with this last bit please. Thanks

  26. #66
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    i have not felt like looking at it for the last few days, maybe at the weekend
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  27. #67

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    ok thanks Pete

  28. #68

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Hi Pete...I'd really appreciate your help with this last bit. Thanks.

  29. #69
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    i have not figured how to tell which budgeted figures are not contracted, do you have any suggestions as to how to pick which they are?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  30. #70

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Quote Originally Posted by westconn1 View Post
    i have not figured how to tell which budgeted figures are not contracted, do you have any suggestions as to how to pick which they are?
    Where is is a budget figure but no contracted figure on the second sheet it is adding it to the variation section which is perfect. But now I need to add to the variation section on the first where there is a contracted figure on the second sheet but no budget figure on the first sheet.

    The coding structure will me the same on both sheets like for example 1234 Grade 2 9999. So if the code exists on either sheet then it should pick up the values.

    Thanks

  31. #71
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    i will think on it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  32. #72

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Quote Originally Posted by westconn1 View Post
    i will think on it
    Thanks Pete

  33. #73
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    i have some code that will detect the contracted amounts, but i am not sure if i like it, or that it will work properly in all cases
    i will play with it a bit more before i post it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  34. #74

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Quote Originally Posted by westconn1 View Post
    i have some code that will detect the contracted amounts, but i am not sure if i like it, or that it will work properly in all cases
    i will play with it a bit more before i post it
    Thanks Pete

  35. #75
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    i decided i did no like the code i was working on previously and have now figured a different approach, so will try to make some example

    this is just to let you know i didn't completely forget about it
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  36. #76
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    here is something for you to test, it appeared to work correctly with a limited sample

    Code:
    Dim c As Collection, c1 As Collection
    With ActiveWorkbook
            Set c = New Collection
            Set c1 = New Collection
            Set dept = .Sheets(1)
            Set dt = .Sheets(2)
            Set vrow = dept.Range("a:a").Find("GRAND TOTAL").Offset(4)
            vrow.Value = "Variation"
            dept.Range("b12:d13").Copy vrow.Offset(1, 1)
            vrow.Offset(2, 2).Value = "Contracted"
            Set vrow = vrow.Offset(4)
            Set rw = dept.Cells(17, 1)
            oset = 0
            nxtrw = 0
            Do
                If IsEmpty(rw.Offset(oset)) Then Exit Do
                If InStr(rw.Offset(oset), "Temp") = 0 And InStr(rw.Offset(oset), "Agency") = 0 And Not rw.Offset(oset, 1) = 0 Then
                    Set fnd = dt.Range("c:c").Find(Left(rw.Offset(oset), 4))
                    If Not fnd Is Nothing Then
                        c.Add fnd.Text, fnd.Text
                        Set fndval = fnd.End(xlDown).Offset(-1, 7)
                        If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(Rows.Count - fnd.Row, 7).End(xlUp)
                        vrow.Offset(nxtrw).Value = rw.Offset(oset).Value
                        vrow.Offset(nxtrw, 1).Value = Round(rw.Offset(oset, 1).Value, 2)
                        
                        vrow.Offset(nxtrw, 2).Value = fndval.Value
                        rwcnt = vrow.Row + nxtrw
                        vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")*-1"
                        nxtrw = nxtrw + 1
                        Else
                        If rw.Offset(oset, 2) = 0 Then vrow.Offset(nxtrw).Resize(, 4).Value = rw.Offset(oset).Resize(, 4).Value: nxtrw = nxtrw + 1
                    End If
                End If
                oset = oset + 1
            Loop
            Set Rng = dt.Cells(6, 3).Resize(Cells(dt.Rows.Count, 3).End(xlUp).Row - 5)
            On Error Resume Next
            For Each cel In Rng
                If Not IsEmpty(cel) Then
                    c.Add cel.Text, cel.Text
                    If Err.Number = 0 Then
                        c1.Add cel.Text, cel.Text
                        Else
                        Err.Clear
                    End If
                End If
            Next
            On Error GoTo 0
            For Each i In c1
                Set fnd = Rng.Find(i)
                If Not fnd Is Nothing Then
                    Set fndval = fnd.End(xlDown).Offset(-1, 7)
                    If fndval.Row = dt.Rows.Count - 1 Then Set fndval = fnd.Offset(dt.Rows.Count - fnd.Row, 7).End(xlUp)
                    vrow.Offset(nxtrw).Value = fnd & " " & fnd.Offset(, 1)
                    vrow.Offset(nxtrw).Offset(, 1).Resize(, 3).Value = fndval.Resize(, 3).Value
                    nxtrw = nxtrw + 1
                End If
            Next
    '        vrow.Offset(nxtrw).CopyFromRecordset r: nxtrw = nxtrw + r.RecordCount
            vrow.Offset(nxtrw + 1, 1).Formula = "=sum(" & vrow.Offset(, 1).Resize(nxtrw).Address(False, False) & ")"
            With vrow.Offset(nxtrw + 1, 1).Resize(, 3)
                .Formula = vrow.Offset(nxtrw + 1, 1).Formula
                .Borders(3).LineStyle = 1
                .Borders(3).ColorIndex = -4105
                .Borders(4).LineStyle = -4119
                .Borders(4).Weight = 4
                .Borders(4).ColorIndex = -4105
            End With
            vrow.Offset(-4).Resize(nxtrw + 6, 4).Interior.Color = 12566463
         .Sheets(1).Select
          .Close True
        End With
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  37. #77

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    Thanks so much Pete. I'll try it and and let you know how it goes.

  38. #78

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    I'm getting a debug error on

    Code:
    For Each i In c1

  39. #79
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copying data from different sheet to sheet 1 on Excel

    any particular error?
    the code, including that line, was tested to run without error
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  40. #80

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Copying data from different sheet to sheet 1 on Excel

    apologies i hadn't set the dim for c and c1.

    The code works Pete but one thing I've noticed is that on sheet 2 we have a code for expenses (code is subs) which should appear on the variation section on sheet 1 as it is unrelated to the variation. what line of code should i add in to exclude this? Thanks
    Last edited by fusion001; Dec 17th, 2020 at 03:21 AM.

Page 2 of 3 FirstFirst 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width