Copying data from different sheet to sheet 1 on Excel
Hi...I have some reports with data on 2 sheets. The first tab is named as the department like for example 4444 and the second sheet which has payment details is named like 4444DT.
What I want to do is that on the first sheet under the GRAND TOTAL after 3 blank rows is copy the data from 17 up to the Pay Total line (as per attached sample) but to exclude any rows where it says Temp or Agency in the description.
The length of the rows will vary report to report but they will all have PAY TOTAL and GRAND TOTAL on the reports. If there is no data for the PAY section then it should carry on to the next one.
I want to copy the data in columns A and B to the new section at the bottom. Then from the second tab (4444DT in the attached example) I want to copy across the totals in column J across to column C in the new section on the first sheet.
On the first sheet the code and the description in in one column so I will need the first 4 left characters to be used to lookup the code column on the second sheet (4444DT) so the correct figures copy across.
On the new section I would like to add a title "Variation" and name the 3 columns as per the attached example.
To make it more clearer I have attached a worked example.
I'd really appreciate help with this please. Thanks.
Re: Copying data from different sheet to sheet 1 on Excel
you can test this to see if it will do as requested
Code:
Dim sht As Long, nxtrw As Long, oset As Long
Dim dept As Worksheet, dt As Worksheet, vrow As Range, rw As Range, fnd As Range, fndval As Range
For sht = 1 To Sheets.Count - 1 Step 2
Set dept = Sheets(sht)
Set dt = Sheets(sht + 1)
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).Resize(, 2).Value = rw.Offset(oset).Resize(, 2).Value
vrow.Offset(nxtrw, 2).Value = fndval.Value
rwcnt = vrow.Row + nxtrw
vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")"
nxtrw = nxtrw + 1
End If
End If
oset = oset + 1
Loop
Next
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
you need to put the entire code within a procedure (Sub), all code must be within a procedure (sub or function), only declares and variables (when required at module level) are in the general section of a module
i did not post a procedure as i did not know how you would call the code, possibly from a button or menu click or maybe part of some other code
Not sure if it makes a difference but I will want to run this macro when it is on sheet 2 (in the example attached sheet 4444DT).
i presumed that you would have several department and wanted to process them all when the code ran, so it would process the first sheet and the second sheet would be the dt sheet for the same department, sheets 3 and 4 would be the next department etc, so it would process however many departments there were in the workbook, of course you did not specify this was so, so i was only guessing
if you only want to process one department from whichever dt sheet you are on you could do that by just changing away from the loop to the activesheet and the sheet before it by index or the department name taken from the dt sheet name
of course if i misunderstood and there is only one department to a workbook then none of that is needed
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...sorry I should have explained it better. I have another code which will run before this one to add a sheet to the first sheet on another workbook and then another code runs to split the workbook. I need the the code you have posted above to run before the workbooks are split and saved as per the example workbook I attached. Here are the 2 codes which I currently run
Code:
Sub splitDT()
Dim wb As Workbook
Dim wsMain As Worksheet
Dim wsNew As Worksheet
Dim j As Long
Dim x As Integer
Dim lr As Integer
Dim lp As Integer
Dim dataEnd As Long
Dim prod As String
Dim rngHeader As Range
Dim mypict As Object
' Application.ScreenUpdating = False
lastRow = Range("L" & Rows.Count).End(xlUp).Row
Worksheets(1).Rows(lastRow & ":" & lastRow).Delete Shift:=xlUp
Columns("G:G").Select
Selection.Insert Shift:=xlToRight, copyorigin:=xlFormatFromLeftOrAbove
Range("G4").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[-1],'[Codes.xls]Codes & Hierarchy'!C1:C7,7,0),"""")"
' Range("G4").Select
lastRow = Range("M" & Rows.Count).End(xlUp).Row
Range("G4").AutoFill Destination:=Range("G4:G" & lastRow)
'Range("G4:G50000").Select
Columns("G:G").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("G3").Select
ActiveCell.FormulaR1C1 = "Description"
Range("A1").Select
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Cut
Columns("O:O").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Set ws = ActiveSheet
With ws
lastRow = .Range("l" & Rows.Count).End(xlUp).Row
For x = 2 To lastRow 'or 2 if headers
If .Range("h" & x).Value = "" Then
.Range("e" & x & ":l" & x).Interior.Color = vbYellow
.Range("e" & x & ":l" & x).Font.Bold = True
.Range("e" & x & ":l" & x).HorizontalAlignment = xlRight
.Range("e" & x & ":l" & x).Borders(xlEdgeLeft).LineStyle = xlDouble
.Range("e" & x & ":l" & x).Borders(xlEdgeRight).LineStyle = xlDouble
.Range("e" & x & ":l" & x).Borders(xlEdgeTop).LineStyle = xlDouble
.Range("e" & x & ":l" & x).Borders(xlEdgeBottom).LineStyle = xlDouble
Columns("E:E").Select
Selection.NumberFormat = "dd/mm/yy;@"
End If
Next x
End With
Application.ScreenUpdating = True
Set wb = ActiveWorkbook
Set wsMain = wb.Worksheets(1)
Set rngHeader = wsMain.Range("a1:l1") 'change depending on how many columns
lr = wsMain.Range("j" & Rows.Count).End(xlUp).Row 'last row of data in B
lp = wsMain.Range("b" & Rows.Count).End(xlUp).Row 'last row with a product
prod = wsMain.Range("a2").Value
With wsMain
dataEnd = .Range("a2").End(xlDown).Row - 1
Set wsNew = wb.Worksheets.Add(after:=wsMain)
.Range("a2:l" & dataEnd).Copy
wsNew.Range("a2").PasteSpecial
wsNew.Range("a:l").Columns.AutoFit
wsNew.Range("a1:l1").Font.Bold = True
rngHeader.Copy
wsNew.Range("a1").PasteSpecial
wsNew.Range("a:l").Columns.AutoFit
wsNew.Range("a1:l1").Font.Bold = True
Application.CutCopyMode = False
Columns("j:l").Select
Selection.NumberFormat = "0.00"
Range("A1").Select
wsNew.Rows("1:4").Insert Shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
With wsNew.Range("a1")
Set mypict = .Parent.Pictures.Insert("C:\Administration\logo.gif")
mypict.Top = .Top
mypict.Left = .Left
mypict.Placement = xlMoveAndSize
mypict.ShapeRange.ScaleHeight 0.8823058409, msoFalse, msoScaleFromTopLeft
mypict.Name = "Picture 1"
[Picture 1].Select
Selection.Cut
Range("A1").Select
ActiveSheet.Pictures.Paste.Select
End With
wsNew.Name = prod & "DT"
For j = dataEnd + 1 To lp
prod = .Range("a" & j).Value
If j = lp Then
dataEnd = lr
Else
dataEnd = .Range("a" & j).End(xlDown).Row - 1
End If
Set wsNew = wb.Worksheets.Add(after:=wb.Worksheets(wb.Worksheets.Count))
.Range("a" & j & ":l" & dataEnd).Copy
wsNew.Range("a2").PasteSpecial
wsNew.Range("a:l").Columns.AutoFit
wsNew.Range("a1:l1").Font.Bold = True
rngHeader.Copy
wsNew.Range("a1").PasteSpecial
wsNew.Range("a:l").Columns.AutoFit
wsNew.Range("a1:l1").Font.Bold = True
Application.CutCopyMode = False
Columns("I:k").Select
Selection.NumberFormat = "0.00"
Range("A1").Select
wsNew.Rows("1:4").Insert Shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
With wsNew.Range("a1")
Set mypict = .Parent.Pictures.Insert("S:\Administration\logo.gif")
mypict.Top = .Top
mypict.Left = .Left
mypict.Placement = xlMoveAndSize
mypict.ShapeRange.ScaleHeight 0.8823058409, msoFalse, msoScaleFromTopLeft
End With
wsNew.Name = prod & "DT"
j = dataEnd
Next j
End With
Call Splitbook
End Sub
This is the second code
Code:
Sub Splitbook()
mypath = ThisWorkbook.Path
Dim ws As Worksheet, p As String
varfolder = InputBox("folder")
p = "C:\Reports\" & varfolder & "\" ' path to master workbooks"
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)
.Sheets(1).Select
.Close True
End With
Else
errlist = errlist & "No valid reports for " & ws.Name & vbNewLine
End If
Next
MsgBox errlist
End Sub
Re: Copying data from different sheet to sheet 1 on Excel
not sure that i understand what you are saying but
I need the the code you have posted above to run before the workbooks are split
maybe
Code:
call newcode
Call Splitbook
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...what I mean is that after the SplitDT code is run I need this new code to run before the splitbook code runs to save the master workbook. Thanks
Re: Copying data from different sheet to sheet 1 on Excel
that was what i was suggesting
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.
Is this code you kindly provided ok to use?
Code:
Sub Copyvariation
Dim sht As Long, nxtrw As Long, oset As Long
Dim dept As Worksheet, dt As Worksheet, vrow As Range, rw As Range, fnd As Range, fndval As Range
For sht = 1 To Sheets.Count - 1 Step 2
Set dept = Sheets(sht)
Set dt = Sheets(sht + 1)
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).Resize(, 2).Value = rw.Offset(oset).Resize(, 2).Value
vrow.Offset(nxtrw, 2).Value = fndval.Value
rwcnt = vrow.Row + nxtrw
vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")"
nxtrw = nxtrw + 1
End If
End If
oset = oset + 1
Loop
Next
End Sub
Re: Copying data from different sheet to sheet 1 on Excel
Is this code you kindly provided ok to use?
it worked with your sample workbook, and should in most cases, but it may not be correct based on the alternative scenarios as in post #4
then maybe
Code:
call Copyvariation
Call Splitbook
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 tried to run the full set of codes in its entirety and I'm getting an error at
Code:
Set vrow = dept.Range("a:a").Find("GRAND TOTAL").Offset(4)
This is because this master workbook is closed.
The splitbook code will pick up the master workbook and attach the second sheet to it so I will need to add the variation part to the first sheet at that time before the workbook is saved.
Sorry for the confusion and not explaining it well. Thanks for your help with this.
Re: Copying data from different sheet to sheet 1 on Excel
sorry i do not understand the hierarchy of your workbooks
if you are working with multiple workbooks you should fully qualify your objects to specify the workbook as well as the worksheet
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 let me explain all the steps of what I do from the beginning.
I have a workbook with over 50 tabs and I run a code to split these tabs into individual workbooks and save each workbook.
The second part is I have a report which is on one sheet. I run the code above to split this report into individual tabs and add totals.
The third part is I run the splitbook code to attach these individual tabs to each of the workbooks saved in the first part.
What I'm trying to add now is to add the Variation section on the first sheet of the workbooks saved in the first step.
So this needs to run after the second code above is run to add the second tab to the workbooks but before the workbook is saved.
The individual workbooks will be closed.
I hope this makes sense and explains things better.
Re: Copying data from different sheet to sheet 1 on Excel
your previous explanation totally lost me, that was why i did not comment further
if you try explaining differently, maybe it will make more sense to me, i could see what you wanted to do initially, but i now have no idea which tabs are in which workbooks, or where you want the results
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
Sorry for the confusion Pete. Let me try and explain from the beginning. I'm going to list it step by step so it's clear to me as well
step 1 - I run a master report which has many sheets. I then run a code to split all the tabs and save each sheet as a new workbook. The new workbooks are saved and then closed.
step 2 - I run another report and run a code to split the report into individual tabs and once this is done each tab is attached to the workbooks saved in step 1 based on the name. the code for this is in post number 5 above.
So this is how it currently works.
What I'm now trying to do and what this is thread is about I would like code to pick up some data from the first and second tab of the saved workbooks and add to a section on the tab 1 of the workbook as described in post 1.
I forgot to mention that the workbooks are closed.
If it is easier I don't mind adding a new tab on the workbook for the "variation" instead of adding it on sheet 1. Similar to how step 2 above works.
I hope I have been able to explain it more clearly this time.
Re: Copying data from different sheet to sheet 1 on Excel
If it is easier I don't mind adding a new tab on the workbook for the "variation" instead of adding it on sheet 1
is this sheet in the master report of step 1?
I forgot to mention that the workbooks are closed.
nice to know, but not a problem
so you need to loop through all the workbooks in some folder, read data from both sheets and add the result to sheet 1 in the original master workbook
is that sheet 1 by name or index?
do you want some space between each workbook data?
which workbook will the code be in?
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
is this sheet in the master report of step 1?
This will be the new workbook which will be saved and closed after all the sheets are split into individual workbooks.
nice to know, but not a problem
so you need to loop through all the workbooks in some folder, read data from both sheets and add the result to sheet 1 in the original master workbook
is that sheet 1 by name or index?
do you want some space between each workbook data?
which workbook will the code be in?
All the workbooks will be in the same folder. Sheet 1 name will be like 4444 like on the example workbook.
I will run the new code after the code has run in post 5 above before the split workbook code.
Please let me know if you need any more information. Thanks.
Re: Copying data from different sheet to sheet 1 on Excel
i have modified part of the second code from post #5, so that the variations should be done at the same time, while the workbook is already opened, but it is not tested in this context
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(sht)
Set dt = .Sheets(sht + 1)
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).Resize(, 2).Value = rw.Offset(oset).Resize(, 2).Value
vrow.Offset(nxtrw, 2).Value = fndval.Value
rwcnt = vrow.Row + nxtrw
vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")"
nxtrw = nxtrw + 1
End If
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
the rest of the procedure remains the same
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
So this will add the variation part on the first sheet rather than add a new sheet?
yes!
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
try
Code:
Set dept = .Sheets(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...The code is now working with errors but it isn't doing what it's supposed to be doing
The "variation" and the column headings are being added on to sheet 1 but then after that the code is just adding another sheet of 4444DT(2) rather than extracting and copying data on to the first sheet.
Re: Copying data from different sheet to sheet 1 on Excel
i tested the code, but had to make some changes for it to work here, i believe changing as below should solve the problem
Code:
Set dept = .Sheets(1)
Set dt = .Sheets(2)
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 that works. I really appreciate all the help you have given with this.
I just need to change the Contracted figures to reverse them so if its showing 1 then it should as -1 as the Budget figures show in reverse format and make the figures to 2 decimal places. Also I would like to add a Total at the end end of each column for the variation section.
Thanks again.
Re: Copying data from different sheet to sheet 1 on Excel
showing 1 then it should as -1
* - 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
I also want to make the columns b:d for the variation part to 2 decimal places
untested
Code:
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)
Last edited by westconn1; Nov 2nd, 2020 at 06:27 AM.
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 did try that before but it was adding another sheet of 444DT rather than reverse the figure in column C. Just tried it again and same issue
it worked correctly when testing here
see previous post for edit
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
ah sorry just checked it again. I need to reverse the symbol in column C rather than the sum column as that will be correct once the symbol is reversed in column C. Sorry and thanks.
Re: Copying data from different sheet to sheet 1 on Excel
so if in column C it says 1 it should show as -1. On the DT it is showing as 1 which is correct for that sheet but on sheet 1 as the budget is showing as -1 the figure in column c should be *-1 to make it a correct comparison.
Re: Copying data from different sheet to sheet 1 on Excel
To make the Variation section stand out I also want to have a light grey background from the cell where it says Variation to the last filled cell in column D. Thanks
Re: Copying data from different sheet to sheet 1 on Excel
Now I need to make the columns to 2 decimal places
see post #30, untested part
and add a total on the bottom
To make the Variation section stand out I also want to have a light grey background from the cell where it says Variation to the last filled cell in column D
untested, but try adding the below lines, between the existing top and bottom lines
Code:
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
i updated the code above to put borders on the totals and a line between the data and the totals, still untested
if some things are in the wrong places, change the offsets to suit
Last edited by westconn1; Nov 2nd, 2020 at 03:51 PM.
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
see updated code above
i do not know why you get an error there, tested to work ok in concept, see what else does not work and i will look later
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