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
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?
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
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
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
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
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.
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.
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
Re: Copying data from different sheet to sheet 1 on Excel
Originally Posted by westconn1
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
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.
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
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.
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.
Re: Copying data from different sheet to sheet 1 on Excel
Originally Posted by fusion001
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.
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
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
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.
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
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.
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
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
Re: Copying data from different sheet to sheet 1 on Excel
Originally Posted by fusion001
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?
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
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
Re: Copying data from different sheet to sheet 1 on Excel
Originally Posted by westconn1
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.
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
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
Re: Copying data from different sheet to sheet 1 on Excel
Originally Posted by westconn1
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
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
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
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
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.