Free Microsoft Excel 2013 Quick Reference

VBA : Creating Macro for variable print ranges in worksheets

I run monthly reports similar to the one below:

13 WEEKS ENDING 01/24/04
$ Vol $ Vol Pct Chg vs YAG
Product 1 $1,611,616 257.03
Product 2 $910,131 176.13
Product 3 $551,210 -62.34
Product 4 $361,410 0.00
Product 5 $356,503 -74.89
Product 6 $323,987 27.29

I am trying to create a macro that will automatically set the print range. The columns are fixed, but the rows will vary depending on the the 'rankings' of info that comes in. I have this, so far:

Sub SetPrintRange()
Dim PrintRange As Range
Set PrintRange = Range("A1:F" & Range("A65536").End(xlUp).Row)
ActiveSheet.PageSetup.PrintArea = PrintRange.Address
End Sub

The column part works, but I can't get the macro to find where the last 'filled' cell is. Now, I have bordered/formatted cells that run after due so that when I 'refresh' everymonth, I don't have to reformat the cells.
I need to figure out how to get the print range to stop at the last 'filled' row, and also so I can have it automatically set every worksheet in the workbook.
Anyone able to help?
Thanks!


Post your answer or comment

comments powered by Disqus
I created an Estimating spreadsheet program using Excel 2007.

The spreadsheet contains about 20 macros (buttons) that i created over a long time along with the help of many very nice people on different forums.

I have been asked by my fellow workers to add an add'l Button (Macro) on our spreadsheet that will allow the user to easily adjust the Print Range of this Spreadsheet (columns and or rows) and then save this new print range to a specific named range. I cannot simply highlight the sheet and then use the set print range function since the print macro i created formats the spreadsheet without showing a sensitive data.

i.e. When the user wants to print: They hit a Button (Macro) that i created that activates a pop up window.

This window asks the user to select via check boxes several choices i.e. paper size, printer selection, orientation of the spreadsheet as well as which section of the spreadsheet to print. For instance - Summary Sheet, Data Backup, Square Foot Summary etc.

Each of these sections have a Named range specific to their section name as well as a specific Print range.

I need help to create a Button that will allow the user to easily adjust each of these print ranges and then save this newly Adjusted range to its specific Range Name. The user may need to increase the number of columns or rows that are preset for each spreadsheet section.

This macro needs to save this new print range so that when the Print Macro is activated this new Range will be set and theadd'l columns or rows thathe user wants to print will be set.

I hope the above makes sense.

Any assistance is appreciated.

Hi All,

I have a sheet fro example from A1 to z100..

I had recoreded a macro to print this area by a button, but daily some date have to remove and some have to add..hense, some time i remains 90 rows and some time 110 rows..

Is there a macro so that print range should automatically adjusted and macro button can print automatically without editing for rows 110 or 90 or whatever..

I hope this is not a hard for you guys..

Thanks in advance..

Is there any macro I could use to set the print range of the spreadsheet to the last row of the spreadsheet?

thanks in advance!

Okay, I am currently working my way through the April 2004 printing of
VBA and Macros for Microsoft Excel. However, I keep running into the same problem and it's starting to become extremely perturbing. On page 150 and 151 in chapter 8:Event Programming, there are two worksheet level event codes which are given as examples. My question is, how the heck do you implement these????? I can reconstruct the data, I can write the code, and I know how to do it with a different code, but it would be nice to be able to do it with a higher level of code if I am ever going to learn. Soooo, could someone please explain how exactly you go about implement these sub routines?

Hi All,

I have a below macro which compares the ranges from two different worksheet. I want this macro to check / compare ranges in each sheet between two workbooks and then give a summary tab whereever it doesn't match.

Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
    If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
    If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
        MsgBox "Can't compare multiple selections!", _
            vbExclamation, "Compare Worksheet Ranges"
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating the report..."
    Set rptWB = Workbooks.Add
    Application.DisplayAlerts = False
    While Worksheets.Count > 1
        Worksheets(2).Delete
    Wend
    Application.DisplayAlerts = True
    With rng1
        lr1 = .Rows.Count
        lc1 = .Columns.Count
    End With
    With rng2
        lr2 = .Rows.Count
        lc2 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    If maxR < lr2 Then maxR = lr2
    If maxC < lc2 Then maxC = lc2
    If lr1 <> lr2 Or lc1 <> lc2 Then
        If MsgBox("The two ranges you want to compare are of different size!" & _
            Chr(13) & "Do you want to continue anyway?", _
            vbQuestion + vbYesNo, "Compare Worksheet Ranges") = vbNo Then Exit Sub
    End If
    DiffCount = 0
    For c = 1 To maxC
        Application.StatusBar = "Comparing cells " & _
            Format(c / maxC, "0 %") & "..."
        For r = 1 To maxR
            cf1 = ""
            cf2 = ""
            On Error Resume Next
            cf1 = rng1.Cells(r, c).FormulaLocal
            cf2 = rng2.Cells(r, c).FormulaLocal
            On Error GoTo 0
            If cf1 <> cf2 Then
                DiffCount = DiffCount + 1
                Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
            End If
        Next r
    Next c
    Application.StatusBar = "Formatting the report..."
    With Range(Cells(1, 1), Cells(maxR, maxC))
        .Interior.ColorIndex = 19
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        On Error Resume Next
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlHairline
        End With
        On Error GoTo 0
    End With
    Columns("A:IV").ColumnWidth = 20
    rptWB.Saved = True
    If DiffCount = 0 Then
        rptWB.Close False
    End If
    Set rptWB = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox DiffCount & " cells contain different formulas!", _
        vbInformation, "Compare Worksheet Ranges"
End Sub

This example macro shows how to use the macro above:

Sub TestCompareWorksheetRanges()
    ' compare two ranges in the active worksheet in the active workbook
    CompareWorksheetRanges Range("A1:A100"), Range("B1:B100")
    ' compare two ranges in two different worksheets in the active workbook
    CompareWorksheetRanges Worksheets(1).Range("A1:A100"), _
        Worksheets(2).Range("B1:B100")
    ' compare two ranges in two different worksheets in two different workbooks
    CompareWorksheetRanges ActiveWorkbook.Worksheets(1).Range("A1:A100"), _
        Workbooks("WorkBookName.xls").Worksheets(1).Range("B1:B100")
End Sub

Thanks a lot for your help in advance.

Could, somebody help me to create a macro for copying certain data in cells
at different rows and different column in Sheet1. to Sheet2, but tabulated
like tab in access (Column,s & Row's).

your immediate action is really apreciated

Thanks

Brahim

I purchased the book, "VBA and Macro for Microsoft Excel" and I am on chapter 1.

In the case, I found out that the code from the website didn't contain the "Invoice.txt".

My question is that "Does Mr. Excel offer those sample data files online?"
If do so, please let me know where to download them.

Thank you

Just a note to let you know that I have been tasked with a project that has been time-consuming, trying to teach people how to maintain an XL database, data integrity, then generate reports (through Pivot Tables, then reformatting, etc.) - all the while tyring to actually do it. Not a pretty sight!

However, today I had time to step back and look at the reports, the Pivot Tables, and the needs. By following the concepts in Chapter 12 in VBA and Macros for MS Excel I have been able to already complete half of the project, just building code step by step. I plan to finish the rest today/tomorrow, and then let the non-XL users try it out, see if they can break it.

So, thank you, thank you, thank you!

Don’t even know if this is the right place to ask this question, but I just received a copy of VBA and Macros for Microsoft Excel and downloaded the support files. I hit a wall right off. On page 21, preparing to record the macro, item 4 instructs to select ‘Invoice.txt.’. I had selected all files and when that brought up what I thought was all the files, guess what was missing? That’s right, ‘Invoice.txt.’. Does anyone know what I’m doing wrong, and so early in the book?

This same question was posted on 1/23 by WSIDER under “Missing Sample Files”, with no answer.

Thanks in advance.

hello. i'm new to excel and i'm really hoping for some serious help here. i have the basics down however i'm really stumped at this point. Let me try to explain my worksheet. the worksheet has a sheet for data which is used for drop list values in the new patient template sheet. the new patient template which is just that a blank entry sheet that the user duplicates and adds a new patient to the worksheet to track the visits made by medical staff. there could be over a hundred new patient sheets (each named by the patient) at any given time.
here is what i need help with: my sheet is setup on a monthly basis so each patient has a total of 4 - 5 weeks listed with entries for everytime a nurse visits that patient. what i need to know is how to search the cell entries for the first time a visit occurred and the date it occurred and this needs to be broken down by the 1st of the month - the 15th and then again for the 16th - the end of month for every patient sheet in the workbook. the ranges are not together that i need to search for example f14:f20, i14:i20, f35:f41 and so on for the first 15 days of the month. this all needs to happen like in a macro or somehow automatically. not sure but any assistance is extremely appreciated!!!

Dear all big brothers and sister,

I have an excel for recording sales manager's products sales.
I use the following macro for sumproduct function. However, in recent days, it doesn't work.
It may be because there are increase more than 200 account managers in the company (i mean integration).

The code is run very smooth if there are only 50 account managers. after adding more than 100 managers in the list, it run very slow.

Appreciate your reply.

I attached the excel. It is simplified version, it runs smooth. However, if you add more account managers (more than 100), it runs very slow.

Sub FillInBlanks()
ActiveWorkbook.Worksheets("sourceAB").Activate
Dim r As Range
Dim res As Range
Set r = Range("a11", Range("b" & Rows.Count).End(xlUp))
Set res = r.Find("")
If Not res Is Nothing Then
    Do
        res.Value = res.Offset(-1, 0).Value
        Set res = r.FindNext(res)
         res.Value = res.Offset(-1, 0).Value
        If res.Value = "" Then res.Value = "N/A"
        Set res = r.FindNext(res)
    Loop Until res Is Nothing
End If
End Sub


Hi,
I have a Forms Command Button w/ a Macro that copy/paste specific variables and range in the workbook to a pre-defined PowerPoint template file (in the same directory as the workbook file).

This macro works but I would like the user to select from a check box window the Graphs or Tables from the Worksheets. The selection would drive the cut/paste to PowerPoint. Any ideas?

Thanks in advance.

I have a worksheet which has 5 lacks of rows and which contains a column name :- VENDOR NUMBER
Now i want to create a macro which calculate the new column called :-Supplier number 2

Formula is :- Supplier Number 2: trim(VENDOR NUMBER)
Supplier Number 2 column must be the last column

how can i write a macro for this

Thanks in advance

Hello,

I am trying to write a Macro that will sum a range of numbers and then go down to the next range of numbers, hten go to the bottom of that range to where I wnat the Sum and then total the second range.

I have tried recording the Macro using keystrokes but it keeps putting the Row,Col range in the Macro.

Here is what I have so far.

Selection.End(xlDown).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
Range("L9").Select
Not all the ranges have the same number of rows in them.

any help is greatly appreciated.

Regards

tattoo

Hi,

In my sheet1 I want to call method ToggleCutCopyAndPaste only for specific range, but it refers to the whole worksheet.

How to refer a call method only to specific range in worksheet?

thank you...

Hi,
How to refer to specific range in worksheet?
In my sheet1 Object I want to call method ToggleCutCopyAndPaste only for specific range.

this code works for whole sheet1.

Private Sub Worksheet_Activate()
Call ToggleCutCopyAndPaste(False)
End With
End Sub

Hello,
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2".

If I replace WhatToPrint with "Report1,Report2" the print macro works. Can anyone help me to understand why it doesn't work when I leave WhatToPrint in?

Really appreciate your help with this!

Jon


	VB:
	
 Macro2() 
     '
     ' Macro2 Macro
     ' Macro recorded 05/05/2009 by JW8836
     '
     
     '
     
    Dim WhatToPrint As String 
     
     
     
    WhatToPrint = Sheets("Reports").Cells(3, 6).Value 
     'sets the variable to equal the contents of cell D3 which contains the formula
     'summarising the print ranges I want to print
     
     
     
    Sheets("Reports").Cells(3, 6).Select 
    ActiveCell.FormulaR1C1 = WhatToPrint 
     ' pastes the variable in cell F3 - just to check that it looks like I want it to
     
    Sheets("Reports").PageSetup.PrintArea = WhatToPrint 
     'uses the variable to set print area - this is where it fails!
     'if you replace the variable with the contents of cell F3 the macro will work
     
    ActiveWindow.SelectedSheets.PrintPreview 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hey Guys, I am trying to create a dynamic chart in VBA. I have defined a bunch of ranges in the excel workbook which correspond a dynamic range. The user is prompted with a userform, and then based on his decisions I want to make a graph. The userform will turn cells either "True" or "False", and if a call is true, I want to add a corresponding named range which represents a dynamic range. So long story short, I create an array of strings which corresponds to the way excel would handle the dynamic range with charting (i.e. graphARR(1)='==Workbook x'!Range_1 and so on and so on, up to 10 entries in graphARR). Later, when graphARR is full of the strings which represent the dynamic ranges I want, i use:

	VB:
	
 graphARR.count 
    With ActiveChart.SeriesCollection.NewSeries 
        .Values = graphARR(icolumn) 
        .XValues = rngchtxval 
        .name = rngchtdata(1, icolumn) 
    End With 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, there is an error when it tries to call graphARR(icolumn). I know there are values in graphARR because when I put in a messagebox higher up in the code, it will return the value (i.e. "==Workbook x'!Range_1"

Does anyone know how to get this to work, or some sort of work around? Thank you very much for all your help!

Hi

What I'd like to figure out is how to get a macro to read from data placed in a cell, and then apply that data to the macro.

like:

Sub Macro1()

Range(">>TBD<<").Select
Selection.sort Key1:=Range("I1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False

End Sub

where the range is read from some other cell on the spreadsheet. so the macro reads what is in the cell telling it what range to sort, and then enters that in the Range("").Select area of the macro.

Otherwise I have to create a separate macro for every area that might possibly apply. I'd like to create an intelligent macro that I tell what range to sort, or delete, or copy, or paste, etc, whatever I want to happen with the range of cells I select.

Any ideas?

Thx

Sorry if the Title is long but i can't think of another way to describe it.

This from the title sounds simple but it's not (at least it's not for me)

I want a macro that when run will do the following
Select Cell J of the row that is already selected.

If you don't understand here is an example
If cell B4 is currently selected and the macro is run the macro selects J4
If Cell AA4 is elected and the macro run it selects cell J4
If Cell A99 is selected and the macro run it selects cell J99

So no matter what cell you are in it will select J(whatever row is currently selected).

(This is probably basic for Excel brainiacs but not for me, I am only just learning code for excel vba)

I cannot use the line:
Range(Whatever).Select
as it is changing the row number depending on which row is currently selected.

I have also tried using the line:
ActiveCell.Offset(0, 8).Select
but that only works if the cell selected when the macro runs is in column A

Can anyone help me?
Thanks a lot if you can.

Im trying (for 5 hours now) to create a macro to create a set of dynamic named ranges. there are column headers in row 5 and 5 rows below that there is the beginning of my data.

Heres where the sticky part comes in: i have another table below my data that references the above table (that is why i need the dynamic named ranges). So, what i need the macro to probably do is to have it count until the first blank space in the column, starting at row 10.

I found code online and adjusted for my own purposes: only issue is that the range is stopping about 8 rows short.
Here is the code:

	VB:
	
 
Sub CreateNames_sample() 
     '
     ' CreateNames_sample Macro
     ' Create Dynamic named ranges in sample
     '
     
     '
     
    Dim wb As Workbook, ws As Worksheet 
    Dim lrow As Long, lcol As Long, i As Long 
     
    Dim myName As String, Start As String 
     
    Dim wsName As String 
     ' set the row number where headings are held as a constant
     ' change this to the row number required if not row 1
    Const Rowno = 5 
     
     ' set the Offset as the number of rows below Rowno, where the
     ' data begins
    Const Offset = 5 
     
     ' set the starting column for the data, in this case 1
     ' change if the data does not start in column A
    Const Colno = 1 
     
     
     
     ' On Error GoTo CreateNames_Error
     
    Set wb = ActiveWorkbook 
    Set ws = ActiveSheet 
     
     ' count the number of columns used in the row designated to
     ' have the header names
     
    lcol = ws.Cells(Rowno, 1).End(xlToRight).Column 
    lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row 
    Start = Cells(Rowno, Colno).Address 
     
     
     'replace blanks in worksheet names with underscore for the purposes of adding range names
    wsName = ws.Name 
    wsName = Replace(wsName, " ", "_") 
     
    wb.Names.Add Name:=wsName & "_lcol", RefersTo:="=COUNT($" & Rowno & ":$" & Rowno & ")" 
    wb.Names.Add Name:=wsName & "_lrow", RefersToR1C1:="=COUNT(C" & Colno & ")" 
    wb.Names.Add Name:=wsName & "_myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & wsName & "_lrow," & wsName &
"_lcol)" 
     
    For i = Colno To lcol 
         ' if a column header contains space or other invalid character etc, replace with underscore
        myName = Replace(Cells(Rowno, i).Value, "/", "_") 
        myName = Replace(myName, " ", "_") 
        myName = Replace(myName, "&", "_") 
        myName = Replace(myName, "(", "_") 
        myName = Replace(myName, ")", "_") 
        myName = Replace(myName, "?", "_") 
        myName = Replace(myName, "", "_") 
         
        If myName = "" Then 
             ' if column header is blank, warn the user and stop the macro at that point
             ' names will only be created for those cells with text in them.
            MsgBox "Missing Name in column " & i & vbCrLf _ 
            & "Please Enter a Name and run macro again" 
            Exit Sub 
        End If 
        wb.Names.Add Name:=wsName & "_" & myName, RefersToR1C1:= _ 
        "=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & "," & wsName & "_lrow)" 
         
nexti: 
    Next i 
     
    On Error Goto 0 
    MsgBox "All dynamic Named ranges have been created" 
    Exit Sub 
     
CreateNames_Error: 
     
    MsgBox "Error " & Err.Number & " (" & Err.Description & _ 
    ") in procedure CreateNames" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The last trick, which i'm completely baffled with, is to then replace the static ranges that are referenced in the table below the original table with the dynamic ranges. all of the ranges correctly correspond to the column, so it should just be an issue of substituting anything like "=COUNTIF(C$10:C$69,">1%")-COUNTIF(C$10:C$69,">2%")" to =COUNTIF(range1,">1%")-COUNTIF(range2,">2%")

If anyone can help i'd be extremely appreciative.

Hi all,

I have a work sheet(sheet1) that contains 700 people's information. Each row contains information for one person. Let's call the first person A. I have to copy certain columns of person A's response to another work sheet(sheet3) in the same file. Then, copy person A's e-mail address and save the sheet as person A's e-mail. Repeat the same steps for the next respondent.

I have created the macro for person A already (including the saving part). Now the question is how to write the loop in the macro. I appreciate your help greatly!

Thanks a lot ,

Jean


	VB:
	
 Macro1() 
     '
     ' Macro1 Macro
     ' Macro recorded 7/5/2007 by Jean Kuo
     '
     
     '
    Range("Q4").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-4]" 
    Range("Q13").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-9]C[-14]" 
    Range("Q14").Select 
    Sheets("Sheet2").Select 
    Range("D4").Select 
    Sheets("Sheet3").Select 
    Range("Q14").Select 
    Sheets("Sheet2").Select 
    Range("D4").Select 
    ActiveCell.FormulaR1C1 = "" 
    Range("C4").Select 
    Selection.Copy 
    Range("D4").Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C17" 
    Range("C4").Select 
    Sheets("Sheet1").Select 
     
    Range("T1").Select 
     
    Sheets("Sheet3").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-10]C[-13]" 
    Range("Q15").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-11]C[-12]" 
    Range("Q16").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-12]C[-11]" 
    Range("Q17").Select 
    Sheets("Sheet2").Select 
    Range("F4").Select 
    Sheets("Sheet2").Select 
    Range("G5").Select 
    Sheets("Sheet3").Select 
    Range("Q17").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-13]C[-10]" 
    Range("Q22").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[42]" 
    Range("Q22").Select 
    Selection.Copy 
    Range("Q23:Q26").Select 
    ActiveSheet.Paste 
    Range("Q23").Select 
    Application.CutCopyMode = False 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[43]" 
    Range("Q24").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[44]" 
    Range("Q25").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[45]" 
    Range("Q26").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[46]" 
    Range("Q26").Select 
    Sheets("Sheet3").Select 
    Range("Q23").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[43]" 
    Range("Q24").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-22]C[44]" 
    Range("Q25").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-23]C[45]" 
    Range("Q26").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-24]C[46]" 
    Range("Q27").Select 
     
End Sub 
Sub Macro2() 
     '
     ' Macro2 Macro
     ' Macro recorded 7/5/2007 by Jean Kuo
     '
     
     '
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-4]" 
    Range("Q13").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-9]C[-14]" 
    Range("Q14").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-10]C[-13]" 
    Range("Q15").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-11]C[-12]" 
    Range("Q16").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-12]C[-11]" 
    Range("Q17").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-13]C[-10]" 
    Range("Q22").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-20]C[42]" 
    Range("Q23").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-21]C[43]" 
    Range("Q24").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-22]C[44]" 
    Range("Q25").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-23]C[45]" 
    Range("Q26").Select 
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-24]C[46]" 
    Range("Q27").Select 
End Sub 
Sub Macro4() 
     '
     ' Macro4 Macro
     ' Macro recorded 7/5/2007 by Jean Kuo
     '
     
     '
    ActiveCell.FormulaR1C1 = "=Sheet1!R[-2]C[-4]" 
    Range("Q13").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-9]C[-14]" 
    Range("Q14").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-10]C[-13]" 
    Range("Q15").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-11]C[-12]" 
    Range("Q16").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-12]C[-11]" 
    Range("Q17").Select 
    ActiveCell.FormulaR1C1 = "=Sheet2!R[-13]C[-10]" 
    Range("Q18").Select 
    Sheets("Sheet1").Select 
    ActiveCell.FormulaR1C1 = "katedavis@cmu.edu" 
    Sheets("Sheet3").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsJean KuoDesktop51507_InternDataSetReportkatedavis@cmu.edu.xls" _ 
    , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ 
    ReadOnlyRecommended:=False, CreateBackup:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,

Do you have a macro for pulling a range of rows from one sheet into another sheet in a different file (adding entire rows of data), & out of the main sheet (deleting entire rows of data)?

I need a macro which will sellect a print range which varies from day to day.

The Range will always start (top left hand corner) at cell C2 and
go to (bottom left of range) the last enty in Column F.

e.g. if the last enty in Column F is in cell F700 then the Print Range is
set to C2:F700

Thanks Ed


No luck finding an answer? You could always try Google.