Results 1 to 17 of 17

Thread: Rename sheets add graph and copy sheet to closed workbooks

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Rename sheets add graph and copy sheet to closed workbooks

    Hi...there is a report with over 100 sheets. I need to rename each sheet to pick out a 4 digits from the sheet name so it will be something like mid(11,4). To the 4 digits I want to add the work "Exp" so the worksheet name will be like 1234Exp.

    Then I want to add a line graph on each worksheet under the data. The data consists of 3 rows starting on row 7 which will be the headings and 13 columns starting on column b which will have the data.

    Once the graph has been plotted I want copy each worksheet to a closed workbook. These closed workbooks will be named with the 4 digit so these worksheets should use this 4 digit filename to identify which workbook to attach the the worksheet to.
    So as an example there will be a workbook named 1234 in a specific folder so the worksheet 1234Exp should attach on worksheet 3 of this workbook.
    I will have a variable at the start to enter the folder where the workbooks are saved.

    I'd really appreciate help with code for this please. Thanks.

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    try recording a macro to create the graph, then convert the recorded code to proper code, that does not use selection for everything

    do you want to rename and add the graphs to the existing sheets in the original workbook, or just for the sheets copied to the closed workbooks?
    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: Rename sheets add graph and copy sheet to closed workbooks

    Quote Originally Posted by westconn1 View Post
    try recording a macro to create the graph, then convert the recorded code to proper code, that does not use selection for everything

    do you want to rename and add the graphs to the existing sheets in the original workbook, or just for the sheets copied to the closed workbooks?
    Hi Pete...i was planning to record a macro to create the graph but I'm not sure how to do a loop to add the graph to all the sheets.

    do you want to rename and add the graphs to the existing sheets in the original workbook, or just for the sheets copied to the closed workbooks?
    The end goal is to add each sheet from this new report to the existing closed workbooks. With regards to adding the graph they just need to be on the new sheet which is added to the existing workbooks. So the option is to either add the graph to all the sheets before each sheet is added to the saved workbooks or to copy over the worksheets to the saved workbooks and then add the graph. Whichever is easier and more efficient.
    I guess the sheets will have to be renamed before they are copied over to the workbooks?
    Thanks

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Rename sheets add graph and copy sheet to closed workbooks

    Here is the macro i recorded for the graph
    Code:
    Sub Graph()
    '
    ' Graph Macro
    '
    
    '
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlLine
        ActiveChart.SetSourceData Source:=Range("A9:O14")
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 1
        ActiveChart.PlotArea.Select
        Selection.Left = 53.92
        Selection.Top = 4.102
        ActiveChart.ChartArea.Select
        ActiveSheet.Shapes("Chart 2").IncrementLeft -370.5
        ActiveSheet.Shapes("Chart 2").IncrementTop 44.25
        ActiveSheet.Shapes("Chart 2").ScaleWidth 4.225, msoFalse, msoScaleFromTopLeft
        ActiveSheet.Shapes("Chart 2").ScaleHeight 1.2048611111, msoFalse, _
            msoScaleFromTopLeft
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        ActiveChart.Axes(xlCategory).Select
        Selection.TickLabelPosition = xlLow
        ActiveChart.SetElement (msoElementChartTitleAboveChart)
        ActiveChart.ChartTitle.Text = "Expenditure Trend"
        Selection.format.TextFrame2.TextRange.Characters.Text = "Expenditure Trend"
        With Selection.format.TextFrame2.TextRange.Characters(1, 17).ParagraphFormat
            .TextDirection = msoTextDirectionLeftToRight
            .Alignment = msoAlignCenter
        End With
        With Selection.format.TextFrame2.TextRange.Characters(1, 17).Font
            .BaselineOffset = 0
            .Bold = msoTrue
            .NameComplexScript = "+mn-cs"
            .NameFarEast = "+mn-ea"
            .Fill.Visible = msoTrue
            .Fill.ForeColor.RGB = RGB(0, 0, 0)
            .Fill.Transparency = 0
            .Fill.Solid
            .Size = 18
            .Italic = msoFalse
            .Kerning = 12
            .Name = "+mn-lt"
            .UnderlineStyle = msoNoUnderline
            .Strike = msoNoStrike
        End With
        Selection.Left = 687.847
        Selection.Top = 0
        ActiveChart.ChartArea.Select
        ActiveSheet.Shapes("Chart 2").IncrementLeft -21
        ActiveSheet.Shapes("Chart 2").IncrementTop 10.5
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        ActiveChart.ChartTitle.Select
        Selection.Left = 8.847
        Selection.Top = 6
        ActiveChart.PlotArea.Select
    End Sub

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Rename sheets add graph and copy sheet to closed workbooks

    Here is the first part of the code which i want to use to format the sheet and rename the each of the sheets on the workbook.

    Code:
    Sub Trend()
    
    
    Dim sht As Worksheet, wb As Workbook, fnd As Range, fname As String, mypict As Object, Prod as string
    
    varfolder = InputBox("Enter Folder")
    
    For Each sht In Workbooks("Trend.xlsx").Sheets
    prod = Mid(Range("a10").Value, 14, 4)
        Set fnd = sht.UsedRange.Find("Total")
            
        If Not fnd Is Nothing Then
            sht.Select      
            
            sht.Rows("1:3").Insert Shift:=xlDown, copyorigin:=xlFormatFromLeftOrAbove
             
            sht.Parent.Windows(1).DisplayGridlines = False
            With sht.Range("a1")
                Set mypict = .Parent.Pictures.Insert("C:\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
      
           Range("E4:E5").ClearContents
                 Set wb = ActiveWorkbook
         
         prod = Mid(Range("a10").Value, 14, 4)
    
    End If
       
    Next
    
    End Sub
    I tried to add the code above for the graph but I get a debug error on the chart name part.

    Thanks
    Last edited by fusion001; Nov 4th, 2020 at 09:35 AM.

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    I guess the sheets will have to be renamed before they are copied over to the workbooks?
    optional, you can rename, format and add graph after copying if yo do not want to change the original sheet
    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: Rename sheets add graph and copy sheet to closed workbooks

    Thanks Pete. I managed to rename the sheets (above code) on the original sheet so may as well add the graph on the original and then copy over to the saved workbooks.
    Can you please help me with adding the graph code in the code above.
    I have managed to add another separate code to copy each sheet to the saved workbooks which I call after all the sheets have been renamed but it would be good to have this bit as part of the main code above so I don't have to call a separate code.
    Also it is copying the sheet on to sheet 2 of the saved workbook but there are already 2 sheets on these workbooks so I want to add this new copied sheet on to next available unused sheet.
    Thanks

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    Code:
    Set s = sh.Shapes.AddChart(xlLine, 53.92, 4.1, 600, 245)
    
    With s.Chart
        .SetSourceData Range("b8:o9")
        .SetElement (msoElementChartTitleAboveChart)
        .ChartTitle.Text = "Expenditure Trend"
        .ChartTitle.Format.TextFrame2.TextRange.Font.Bold = msoCTrue
    End With
    this will create a chart on a worksheet represented by sh worksheet object, you can change to use any sheet object you already have
    change chart position range and anything else you want
    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: Rename sheets add graph and copy sheet to closed workbooks

    Thanks Pete. How can I set the amount increments dynamically for each sheet?

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    How can I set the amount increments dynamically for each sheet?
    is that the range of values?
    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: Rename sheets add graph and copy sheet to closed workbooks

    Quote Originally Posted by westconn1 View Post
    is that the range of values?
    Yes range of values. Thanks

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    The data consists of 3 rows starting on row 7 which will be the headings and 13 columns starting on column b which will have the data.
    is any part of this fixed for all sheets?
    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: Rename sheets add graph and copy sheet to closed workbooks

    Quote Originally Posted by westconn1 View Post
    is any part of this fixed for all sheets?
    The rows and column headings will be fixed on all sheets. Thanks

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    if the rows and columns are the same for all sheets, why do you need to set the increments dynamically?
    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

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Rename sheets add graph and copy sheet to closed workbooks

    Quote Originally Posted by westconn1 View Post
    if the rows and columns are the same for all sheets, why do you need to set the increments dynamically?
    sorry Pete. The row and column headings are the same for all sheets but the values in row/column will be different for each sheet so I want the values range on the Y axis of the graph on each sheet to be in the range of each sheet.

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

    Re: Rename sheets add graph and copy sheet to closed workbooks

    see if this does what you want
    Code:
    Set s = sh.Shapes.AddChart(xlLine, 53.92, 4.1, 600, 245)
    rws = Cells(Rows.Count, 2).End(xlUp).Row - 7
    Set Rng = Range("b8").Resize(rws, 14)
    With s.Chart
        .SetSourceData Rng
        .SetElement (msoElementChartTitleAboveChart)
        .ChartTitle.Text = "Expenditure Trend"
        .ChartTitle.Format.TextFrame2.TextRange.Font.Bold = msoCTrue
    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

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2014
    Posts
    367

    Re: Rename sheets add graph and copy sheet to closed workbooks

    Thanks Pete. I'll try it out and let you know.

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