Free Microsoft Excel 2013 Quick Reference

- Need to chart multiple variables
- Chart multiple variables
- I need to count multiple variables so I can chart results!
- Macro to create multiple pareto charts with variable data
- Need help creating multiple plots
- Showing lookup result with multiple variables
- Range reference problem placing charts
- Need to test for multiple variables in columns
- Showing lookup result with multiple variables
- How do I show multiple time durations in a column bar chart?
- Help with making multiple embedded charts & dealing with changing data series sizes
- Need help on Line Chart with Multiple Variables
- Need Help with Data Labeling Macro
- Printing Multiple Graphs on the same sheet
- Automate Day Count And Chart Results
- Multiple variables and criteria
- Creating multiple graphs using variable ranges
- Multiple "Range" with the "Cells" property?
- Create Multiple Graphs/Charts From Data Groups
- VBA- Changing series in chart

I have seven (7) variables in one column, which each have possible seventeen (17) variables. I need to create a small table of the total counts so I can chart the results. I have tried "COUNTIF" but I use too many arguments...Please help!

Cobra

VB:AutoChartRejects() Dim rngData As Range Dim rngArea As Range Dim TPos As Integer Dim objChart As ChartObject Dim xx As Series Dim columnF As Range Set columnF = Intersect(Range("F1").EntireColumn, ActiveSheet.UsedRange) columnF.Value = Evaluate("IF(ROW(" & columnF.Address & "),IF(" & columnF.Address & """"",TRIM(" & columnF.Address & "),""""))") Set rngData = Range("H2", Cells(Rows.Count, 8).End(xlUp)).SpecialCells(xlCellTypeConstants) Set xrngData = rngData.Offset(0, -2) TPos = 0 Worksheets.Add().Name = "Charts" For Each rngArea In rngData.Areas ' Range(xrngData, rngData).Sort _ ' Key1:=.Range(.Cells(1, 3), .Cells(lastrow, lastCol)), Order1:=xlDescending, _ ' MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes TPos = TPos + 150 With objChart.Chart Do While .SeriesCollection.Count > 0 .SeriesCollection(1).Delete Loop With .SeriesCollection.NewSeries .Values = rngArea .XValues = rngData.Offset(0, -2) .Name = rngArea.Cells(1, 1).Offset(-1, -7) 'name for legend, needs to be name for Chart Title .ChartType = xlColumnStacked End With objChart.Activate .HasTitle = True .ChartTitle.Characters.Text = "Reject Code by Machine" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Defect Type" .ChartTitle.Font.Size = 9 .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# Rejects" With .PlotArea .Top = 10 .Left = 10 .Width = 152 .Height = 122 End With End With Next End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have a spreadsheet where I'm collecting lots of data, where each data set is in the same format. I want to plot the data using a simple line chart, but I need to find a way to easily make many copies of the chart using each of the different data sets. It is taking me way too long to grab each set of data and use the Insert-Chart command and GUI wizard to make the plot.

My data looks like this, in CSV format for ease of viewing. X is the X-axis variable and y1-y4 are sets of values to be plotted on the y axis.

X, y1, y2, y3, y4

1, 10, 11, 12, 13

2, 20, 21, 22, 23

3, 30, 31, 32, 33

... and so on ... then it repeats ...

X, y1, y2, y3, y4

1, 10, 11, 12, 13

2, 20, 21, 22, 23

3, 30, 31, 32, 33

... and so on, and this pattern repeats lots of times...potentially hundreds of times. But the relationships of the cells is always identical. So it would be great if I could set up a plot once, then copy that cell data or macro or whatever and apply it to each data set.

Any ideas how to accomplish this? I can't get anything to repeat the way I need it to.

Thanks in advance,

Andy

I have been working on solving this one problem for three weeks, with no

success. I have been through the discussion forums, but not quite been able

to find the information I am seeking. Can you help? I would be very

appreciative of any suggestions you could provide.

The problem will be easiest to understand if you view the file, which can be

downloaded from my Comcast storage:

http://home.comcast.net/~jaredprice1...file_Sheet.xls

This is a spreadsheet used for scoring tests. This sweet spreadsheet will

automate everything once it is completed.

The problem at hand pertains to the "Math" sheet, cells D2-E10. Once I can

get those cells to function correctly, I simply need to get that information

into it's proper place on the "Score Summary" sheet and the long project is

complete.

There are four levels of tests: A, D, M, and E. Example: if the test being

scored is a level A, I will enter "A" into cell E16 on the "Score Summary"

sheet. This will affect several other cells, particularly those found in

columns B-D , which will be hidden. The Scale Score and Grade Level are

affected by the letter in this box and calculated accordingly.

The Scale Score and Grade Level is automatically calculated for Reading,

Language, Vocabulary, and Spelling. With Mathematics, however, there are two

tests, and the sum of correct answers are added into one raw score (cell

I15). However, the two math tests carry different amounts of weight, and so

the scale score and grade level has to be calculated from the charts provided

with the sheet (See "Math A", "Math D", "Math M", and "Math E".

Example: if test A was taken, and the tester scored 8 correct in Mathematics

Computation and 17 correct for Applied Mathematics, I would need to enter 8

into cell "Math!C2" and 17 into cell "Math!C7". I would then need the

information from "MathA!L39" to appear in cell "Math!D12" and the information

from "MathA!L40" to appear in cell "Math!E12". This information can then be

easily moved into the correct place the Score Summary sheet.

Another example, if necessary: if test D was taken, and the tester scored 20

on Mathematics Computation and 20 on Applied Mathematics, I would need to

enter 20 into cell C3 and 20 into cell C8 (on the "Math" sheet. I would then

need the information from "MathD!X45" to appear in cell "Math!D13" and the

information from cell "MathD!X46" to appear in cell "Math!E13".

Thank you so much for your assistance and ideas.

Jared Price

The chart locations depend on the column headers of the source data.

My problem is that the range statement does not allow for variable cell

references.

I have tried several variations of Range. The only reference that works is

the format "LetterNumber" as in Range("A1")

Range(Cells(x,y)) does not work

Range(Cells(x,y),Cells(x,y)) does not work

Range("A" & x) does not work

var1="a" & str(x)

Range(var1) does not work.

I need to increment the placement of the top left corner of each graph.

Can anyone help ?

Sheet8.Activate

Set ChtObj = ActiveSheet.ChartObjects("MyChart")

ChtObj.Top = Range(cells(p,q)).Top

ChtObj.Left = Range(cells(p,q)).Left

p=p+20

Loop

The first key question I have to ask is "Is the building suitable for the occupants?" (The "yes" "no" answer is my first value).

If the answer is Yes, even thought it is suitable, I ask if the cost to fix it is greater than the cost to replace it, in which case the building is rebuilt, otherwise it is recapped.

If the answer is No, I ask if the cost to fix it is greater than 75% of the cost to replace it, in which case the building is rebuilt, otherwise it is recapped.

The chart is easy, but I don't know how to program getting the right answer.

I have been working on solving this one problem for three weeks, with no

success. I have been through the discussion forums, but not quite been able

to find the information I am seeking. Can you help? I would be very

appreciative of any suggestions you could provide.

The problem will be easiest to understand if you view the file, which can be

downloaded from my Comcast storage:

http://home.comcast.net/~jaredprice1...file_Sheet.xls

This is a spreadsheet used for scoring tests. This sweet spreadsheet will

automate everything once it is completed.

The problem at hand pertains to the "Math" sheet, cells D2-E10. Once I can

get those cells to function correctly, I simply need to get that information

into it's proper place on the "Score Summary" sheet and the long project is

complete.

There are four levels of tests: A, D, M, and E. Example: if the test being

scored is a level A, I will enter "A" into cell E16 on the "Score Summary"

sheet. This will affect several other cells, particularly those found in

columns B-D , which will be hidden. The Scale Score and Grade Level are

affected by the letter in this box and calculated accordingly.

The Scale Score and Grade Level is automatically calculated for Reading,

Language, Vocabulary, and Spelling. With Mathematics, however, there are two

tests, and the sum of correct answers are added into one raw score (cell

I15). However, the two math tests carry different amounts of weight, and so

the scale score and grade level has to be calculated from the charts provided

with the sheet (See "Math A", "Math D", "Math M", and "Math E".

Example: if test A was taken, and the tester scored 8 correct in Mathematics

Computation and 17 correct for Applied Mathematics, I would need to enter 8

into cell "Math!C2" and 17 into cell "Math!C7". I would then need the

information from "MathA!L39" to appear in cell "Math!D12" and the information

from "MathA!L40" to appear in cell "Math!E12". This information can then be

easily moved into the correct place the Score Summary sheet.

Another example, if necessary: if test D was taken, and the tester scored 20

on Mathematics Computation and 20 on Applied Mathematics, I would need to

enter 20 into cell C3 and 20 into cell C8 (on the "Math" sheet. I would then

need the information from "MathD!X45" to appear in cell "Math!D13" and the

information from cell "MathD!X46" to appear in cell "Math!E13".

Thank you so much for your assistance and ideas.

Jared Price

jaredaprice@hotmail.com

P.S. If you find yourself wondering about some of the less intelligent

design using lookup statements and such, just know that it seemed easier at

the time. I didn't build the four charts until later in the process.

Completion Time (X-axis).

Each column hsould represent a different person.

Currently I can only get Excel to plot one time on the X-axis for multiple

persons.

I am plotting from auto-filtered data and using MS Excel 2003 (11.8033.8028).

I am attaching an example of what I am looking to do. The raw data is on the worksheet called COMPRESS & the charts I made by hand are on Sheet1.

Another thing I need to do is have the charts data series adjust for the ammount of trials I have. Im my example I am useing 30 trials, but I will need the charts to adjust to show trial sizes of 5, 6, 10, 15, & 20. I was thinking of haveing a cell in COMPRESS that I can type in how many trials I have & then have VBA referance that number as a variable when making the charts, but I am not sure how to go about this.

Any help would be greatly appreciated.

This is my first post on this forum, so bare with me.

I am trying to chart the performance of telesales personnel at my workplace.

I would like to use a line chart to 'chart' their performance over a period of time (so Y-axis = Date).

The Variables are:

Total Call Time

Total Talk Time

I would like the option to expand the chart as time progresses, so I can continue to update the chart with staffs performance, and get a clear picture of their avg. performance over time.

Please find attached the data sheet.

Any help on this would be greatly appreciated.

Many thanks

Alex

VB:[/COLOR][/SIZE][/FONT][B][TABLE="width: 100%"] [TR] [TD="class: contentRight"][TABLE="class: tborder, width: 100%, align: center"] [TR] [TD="class: alt1, bgcolor: #FCFCFC"][FONT=Arial][SIZE=2][COLOR=#000000] Sub AttachLabelsToPoints() 'Dimension variables. Dim Counter As Integer, ChartName As String, xVals As String ' Disable screen updating while the subroutine is run. Application.ScreenUpdating = False 'Store the formula for the first series in "xVals". xVals = ActiveChart.SeriesCollection(1).Formula 'Extract the range for the data from xVals. xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _ Mid(Left(xVals, InStr(xVals, "!") - 1), 9))) xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1) Do While Left(xVals, 1) = "," xVals = Mid(xVals, 2) Loop 'Attach a label to each data point in the chart. For Counter = 1 To Range(xVals).Cells.Count ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _ True ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _ Range(xVals).Cells(Counter, 1).Offset(0, -1).Value Next CounterEnd SubI have a macro that I´ve been using to label series data points on a chart with a desired name rather than with the coordinate points. The macro works very well except for one thing, it is only meant to make one series. I would like to add a loop to it to make it be able to plot multiple series and label their data points. I´m not sure if this would work but ideally I would like to have the code distinguish a new series whenever there is a blank row in the data. So For example: AG32 4 5 BC78 3 6 HA23 2 0 AN32 4 6 JK12 2 6 I would Like the code To creat two series one With AG32-HA23 With labeled data points And one With AN32-JK12 With labeled data points, And on the same graph. The code I have looks Like this: [/TD] [/TR] [/TD] [/TR]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:[/TABLE][/TD] [/TR]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:[/TABLE][/TD] [/TR]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:[/B][B][TABLE="width: 100%"]If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

[TR]

[TD="class: contentRight"][TABLE="class: tborder, width: 100%, align: center"]

[TR]

[TD="class: alt1, bgcolor: #FCFCFC"][FONT=Arial][SIZE=2][COLOR=#000000]I think I could add series with an outer loop to the format routine that looks something like

I have a workbook of many spreadsheets. There are 20 spreadsheets all having 20+ graphs(number unknown). I am trying to print all of these graphs automatically. I have this code below which does that but it only prints 1 graph per page. Is there any way to print 4-6 graphs per page? Also, I tried writting a code to transfer all the graphs into powerpoint(1 graph per slide), but that it did not work i kept getting errors. Both codes are below. It doesnt matter to me how it happens. But I just need to print all the graphs.. a few per page. Any help would be great. During the printing process as well does anyone know how to stick a page in between the sheets to print? Such as a title page so people know what the next set of data on the sheet is?

Here is code that prints 1 graph per page

VB:and here is the PP one. THis one i did not create but i get errorsMacro5() 'This macro will print all embedded charts in the active workbook ' Application.ScreenUpdating = False Dim Sht As Object Dim Cht As ChartObject Dim strCurrentPrinter As String strCurrentPrinter = Application.ActivePrinter ' store the current active printer On Error Resume Next ' ignore printing errors Application.ActivePrinter = "[URL="file://Torfnp01ETBPR102"]Torfnp01ETBPR102[/URL] on Ne09:" ' change to another printer For Each Sht In ActiveWorkbook.Sheets ' for each sheet in the workbook For Each Cht In Sht.ChartObjects 'for each chart in the sheet Cht.Activate 'activate the chart ActiveChart.ChartArea.Select 'select the chart 'ActiveWindow.SelectedSheets.PrintOut 'print the chart Sheets("Sheet30").Select ActiveSheet.Paste Next Next Application.ActivePrinter = strCurrentPrinter ' change back to the original printer On Error Goto 0 ' resume normal error handling End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:option3() For Each Sht In ActiveWorkbook.Sheets ' for each sheet in the workbook For Each Cht In Sht.ChartObjects 'for each chart in the sheet Cht.Activate 'activate the chart ActiveChart.ChartArea.Select 'select the chart Worksheets("sheet2").Paste 'print the chart Next Next End Sub Sub CreateNewPowerPointPresentation() ' to test this code, paste it into an Excel module ' add a reference to the PowerPoint-library this is done from the Tools ---> References menu path and you 'need to find the microsoft powerpoint check box and check it. Then excel can use ppt objects within itself Dim pptApp As PowerPoint.Application Dim pptPres As PowerPoint.Presentation Dim pptSlide As PowerPoint.Slide Dim pptShape As PowerPoint.Shape Dim i As Integer, strString As String Dim Graphcount As Integer Count = 0 'initialise count variable i = 1 'returns the number of charts on the sheet at the time the macro is run. 'User customises the worksheets name to sheet that holds all the charts Graphcount = Worksheets("Reason Code Metrics").ChartObjects.Count Set pptApp = CreateObject("PowerPoint.Application") Set pptPres = pptApp.Presentations.Add(msoTrue) ' create a new presentation ' or open an existing presentation ' Set pptPres = pptApp.Presentations.Open("C:FoldernameFilename.ppt") Do While i < Graphcount ' starts a loop to copy charts ActiveSheet.ChartObjects(i).Activate ' selects the chart object by its index number ActiveChart.ChartArea.Select ActiveChart.ChartArea.Copy With pptPres.Slides Set pptSlide = Add(.Count + 1, ppLayoutTitleOnly) ' add a slide End With With pptSlide .Shapes(1).TextFrame.TextRange.Text = "Slide Title" 'edit to put a generic title on each slide or ' take this line out if you dont want a generic slide title to appear on each slide .Shapes.PasteSpecial ppPasteDefault With .Shapes(.Shapes.Count) ' sizes the graph on the slide .Left = 120 .Top = 125.125 .Width = 480 .Height = 289.625 End With End With Application.CutCopyMode = False ' end cut/copy from Excel Set pptSlide = Nothing i = i + 1 ' increment the graph count to copy the next chart on the excel sheet Loop On Error Resume Next ' ignore errors On Error Goto 0 ' resume normal error handling Set pptPres = Nothing pptApp.Visible = True ' display the application 'pptApp.Quit ' or close the PowerPoint application Set pptApp = Nothing End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please note that the data on the two worksheets is not the same and just provided as an example. Reports are run on monthly basis for multiple divisions (A,B, C, D, E, etc) from case management system and exported to Excel. Number of rows is variable. Reports show all Open (O) and Reopen (R) Cases for each respective division (displayed in Column F). The sheet marked 'Raw Data' is what I get when I pull the data. First thing when I get the "raw data" I have to join the data in D1+D2, E1+E2, G1+G2 so it formats the row headings properly. Columns A through D are just informational. If you look at the "What I'm Working On" sheet displays what I'm trying to do. I add column H and I. I insert current date in Column H and then count the number of days that have lapsed since last activity and put that in Column I. I then determine how many fall within a given range. I then chart the data into three pie charts. One for Open Cases, one for Reopen Cases, and then one for a total of all cases. I then need to set borders around all of the data in columns A through I. I set row A as repeating on all pages. I then need to set a header which changes for each report but I put in "Case Load Detail Listing, Repective Division Letter, Date Range of Report, and Run date" and also a footer that just gives page number (maybe set a User Interface and I can enter that data as needed). 8 1/2 x 11 landscape print. Charts need to be the very first page and followed by the detail case listing. I usually print this to a pdf for presentation to staff.

Amount Loan % Term Interest Rate

0 35.00 1 6.95%

20000 40.00 1.5 6.46%

34500 45.00 2 5.97%

49000 50.00 2.5 5.48%

63500 55.00 3 4.99%

78000 60.00 3.5 4.50%

The organization will give the recipent a percentage of the total loan requested based on its amount as described in the table; however, if the annual income is less than 35ooo and the service years is less than 5 years and the house equity is less than half of the house value, the loan approved will be only 10000.

I need a formula to express all these variables and critera and someone reccomended that i use the "IF" and "AND" functions but im still not sure if i should use them. Any help that you could provide would be greatly appreciated. Thank you for your time. -Dave

First, this is an excellent forum! I can see myself wading around here quite a bit.

The issue I am having is thus...

I need to create a macro from a workbook that has a variable amount of data. I need to create graphs that correspond to a 1 hour period. I am able to find the length of the workbook and calculate the number of graphs that need to be made. I created a for loop to iterate through the code to create the graphs but am having a problem setting the ranges for the graphs within the loop. The range must increment along with the loop to access different portions of the data.

Here is the code for the graphing portion....

VB:make_graphs() ' ' make_graphs Macro ' Macro recorded 1/19/2006 by rich ' ' Dim i As Integer Dim StartPoint As Integer Dim EndPoint As Integer Dim rStartPoint As String Dim rEndPoint As String StartPoint = 2 EndPoint = 61 For i = 1 To 5 rStartPoint = "A" & StartPoint rEndPoint = "E" & EndPoint Range("rStartPoint:rEndPoint").Select Charts.Add ActiveChart.ChartType = xlLine ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("StartPoint:EndPoint"), PlotBy _ :=xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Exchange 1000 - 1100" .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _ "Time in 1 minute intervals" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "Blue = Paging, Pink = Packets Sent, Yellow = Packets Recieved, Lt. Blue = CPU" End With StartPoint = StartPoint + 60 EndPoint = EndPoint + 60 Next i End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any help you guys could give me would be greatly appreciated!!

Thanks in advance,

Rich

VB:I have a following piece of code used to determine range for a chart:Range("B55:D55,I55").SelectIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:but I would like to add a second range to it - like the cell "I55" in:myrange = Worksheets("Chart").Range(Cells(startx, column), Cells(weekx, column))If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:As I'm using variables to determine the range I need to use the "Cells" property!Range("B55:D55,I55").SelectIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Is this possible!?

VB:...this makes 18 charts before saying it overflowed. Also, the series of each chart doesn't change with the loop. It's the same data plotted on every graph instead of starting from C11:C1910 and then going to the next chart C1911:C3810, etc. for Y values, and X values should start from A11:A1910 to A1911:A3810, etc. For example.Graphs() 'Declare Variables Dim Startpoint As Integer Dim Endpoint As Integer Dim count As Integer Dim xStart As String Dim xEnd As String Dim NumberSets As Integer Dim yStart As String Dim yEnd As String Dim DataSet As Integer Dim Data 'Define Variables Startpoint = 11 'The first set always starts in row 11 Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10 NumberSets = Range("L7").Value 'number of times I need the loop to work count = 1 DataSet = Range("L4").Value 'set an int for the number of data points in a set 'Add Graphs Loop For count = 1 To NumberSets Data = DataSet * (count - 1) 'THIS IS WHERE DEBUGGER HATES ME 'More Variables xStart = "a" & (Startpoint + DataSet) xEnd = "a" & (Endpoint + DataSet) yStart = "c" & (Startpoint + DataSet) yEnd = "c" & (Endpoint + DataSet) Charts.Add 'Create scatter plot chart ActiveChart.ChartType = xlXYScatter 'Data Source ActiveChart.SetSourceData Source:=Sheets("Control Data").Range(yStart & ":" & yEnd), _ PlotBy:=xlColumns 'X-Axis Info ActiveChart.SeriesCollection(1).XValues = Sheets("Control Data").Range(xStart & ":" & xEnd) ActiveChart.SeriesCollection(1).Name = "DATA" ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart " & count With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Dr Blade Nip Data Set #" & count .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X Coordinate (mm)" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Slope Z" End With ActiveChart.HasLegend = False Next count End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any help would be much appreciated. Thanks!

ActiveChart.ChartArea.Select

Sheets("MW-2").Select

Sheets("MW-2").Copy Before:=Sheets(1)

Sheets("MW-2 (3)").Select

Sheets("MW-2 (3)").SeriesCollection(1).Select

ActiveChart.SeriesCollection(1).XValues = "=ExportWLtargets!R2C4:R71C4"

ActiveChart.SeriesCollection(1).Values = "=ExportWLtargets!R2C4:R71C4"

ActiveChart.SeriesCollection(1).Name = "=ExportWLtargets!R2C1"

Basically the portion that needs to 'loop' is "=ExportWLtargets!R2C4:R71C4" , so the Row Column needs to change but I will still be exporting from this 'ExportWLtargets' sheet. I have tried using the

Sheets("ExportWLtargets").Range(Cells(2, 4), Cells(71, 4)) in place of "=ExportWLtargets!R2C4:R71C4" but have received an error. Is there any other way to write

"=ExportWLtargets!R2C4:R71C4" such that there could be a variable in the place of the 2, 4, 71 and 4? Thank you for any help you can give me.