-
Nov 3rd, 2020, 01:27 PM
#1
Thread Starter
Hyperactive Member
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.
-
Nov 4th, 2020, 03:22 AM
#2
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
-
Nov 4th, 2020, 03:38 AM
#3
Thread Starter
Hyperactive Member
Re: Rename sheets add graph and copy sheet to closed workbooks
Originally Posted by westconn1
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
-
Nov 4th, 2020, 04:34 AM
#4
Thread Starter
Hyperactive Member
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
-
Nov 4th, 2020, 07:36 AM
#5
Thread Starter
Hyperactive Member
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.
-
Nov 5th, 2020, 04:14 AM
#6
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
-
Nov 5th, 2020, 04:26 AM
#7
Thread Starter
Hyperactive Member
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
-
Nov 7th, 2020, 05:06 AM
#8
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
-
Nov 7th, 2020, 08:30 AM
#9
Thread Starter
Hyperactive Member
Re: Rename sheets add graph and copy sheet to closed workbooks
Thanks Pete. How can I set the amount increments dynamically for each sheet?
-
Nov 8th, 2020, 03:10 AM
#10
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
-
Nov 8th, 2020, 08:18 AM
#11
Thread Starter
Hyperactive Member
Re: Rename sheets add graph and copy sheet to closed workbooks
Originally Posted by westconn1
is that the range of values?
Yes range of values. Thanks
-
Nov 9th, 2020, 04:03 AM
#12
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
-
Nov 9th, 2020, 04:27 AM
#13
Thread Starter
Hyperactive Member
Re: Rename sheets add graph and copy sheet to closed workbooks
Originally Posted by westconn1
is any part of this fixed for all sheets?
The rows and column headings will be fixed on all sheets. Thanks
-
Nov 9th, 2020, 04:48 AM
#14
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
-
Nov 9th, 2020, 05:07 AM
#15
Thread Starter
Hyperactive Member
Re: Rename sheets add graph and copy sheet to closed workbooks
Originally Posted by westconn1
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.
-
Nov 10th, 2020, 02:49 AM
#16
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
-
Nov 10th, 2020, 03:25 AM
#17
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|