Page 1 of 3 123 LastLast
Results 1 to 40 of 86

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

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.
    Attached Files Attached Files

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

    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

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    Hi Pete

    Thanks so much. I get a compile error: Invalid outside procedure on For sht = 1 To Sheets.Count - 1 Step 2

    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).

    Thanks.

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

    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

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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
    Thanks for your help with this.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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

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

    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

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.

    Thanks so much for your help with this.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    Hi Pete are you able to assist with this please? Thanks

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.

    Thanks so much for your help with this.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    I am attaching an example workbook showing the variation on the third sheet. I don't mind doing it this way if it is easier. Thanks.
    Attached Files Attached Files

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  19. #19

    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
    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.

  20. #20
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  21. #21

    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 out. So this will add the variation part on the first sheet rather than add a new sheet?

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  23. #23

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    Hi Pete...I have tried the code and I am getting a Subscript out of range error on
    Code:
    Set dept = .Sheets(sht)
    part.
    It looks like it is trying to add a second worksheet named 4444DT.
    Any ideas? Thanks

  24. #24
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  25. #25

    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 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.

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

    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

  27. #27

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.

  28. #28
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  29. #29

    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
    * - 1
    Thanks Pete. I'm getting a debug so I must be adding it in the wrong place. Where in here do I need to add it
    Code:
    vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")"
    I also want to make the columns b:d for the variation part to 2 decimal places and add a Total for these 3 columns. Thanks.

  30. #30
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    try
    Code:
                        vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")*-1"
    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

  31. #31

    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
    try
    Code:
                        vrow.Offset(nxtrw, 3).Formula = "=sum(b" & rwcnt & "-c" & rwcnt & ")*-1"

    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

  32. #32
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  33. #33

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    ok i copied and pasted your edit above and this time it is not adding the sheet twice but it is not reversing the symbol in column C.
    Corrected now.
    Last edited by fusion001; Nov 2nd, 2020 at 07:22 AM.

  34. #34

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.

  35. #35

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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.

  36. #36

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    ok sorted.
    Code:
    vrow.Offset(nxtrw, 2).Value = fndval.Value * -1
    Now I need to make the columns to 2 decimal places and add a total on the bottom. Thanks

  37. #37

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    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

  38. #38
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  39. #39

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

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

    Thanks Pete. I'm getting a debug error "Application-defined or object-defined error" on this part
    Code:
    vrow.Offset(, 1).Resize(nxtrow, 3).Interior.Color = 12566463

  40. #40
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

Page 1 of 3 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