Visual Basic :: Print A Worksheet Range From Vb 6.0

Retrieving ADO Recordset From Worksheet Range
I am trying to use the Excel ODBC desktop driver to retrieve a named range into an ADO recordset. I would like to retrieve this data from an opened workbook.

I've tried:

Dim cn2 As Connection
Dim tblDates As ADODB.Recordset
Dim rDates As Range, stCon As String

Set cn2 = CreateObject("ADODB.Connection")
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=DailyTest.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
cn2.Open stCon

Set tblDates = cn2.Execute("Select * From [TheDates]")

I get this error:

"The Microsoft Jet database engine could not find the object 'TheDates'. Make sure the object exists and that you spell its name and the path name correctly."

The thing is, the workbook "DailyTest.xls" is open and it does contain a named range named "TheDates".

I've also tried to open the connection object with:
'cn2.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
'"DriverId=790;" & _
'"Dbq=DailyTest.xls"

I get the same error.

How can I reference this named range in the above SELECT statement?
This is the range (from the immediate pane):

?Workbooks("DailyTest.xls").worksheets("Data").range("TheDates").Addre ss
$B$4:$B$34

?Workbooks("DailyTest.xls").worksheets("Data").range("TheDates").Name
=Data!$B$4:$B$34

Print Range Of Pages, Find Length Of Document To Print
Hi, I am trying to accomplish two things.
1) I would like to find out how many pages would print out, IF I were to print. ie. Warn the user: "You have asked to print out 500 sheets at $4.50 a sheet."
2) I would like to choose a range of pages to print out, without that range set as the "default" the next time the print function is called. So, my current printing method below is not what I would like. (Unless there is a way to restore defaults?) It displays the normal print dialog, and remembers the last print range entered by the user.

I am displaying web pages on a form within the webbrowser control. Some are very long, so these features are important to me. How it is done is less important. Any advice is appreciated. Thanks!

Here is my very simple example code:

Code:
Option Explicit

'Load a web page
Private Sub Command1_Click()
WebBrowser1.Navigate ("http://www.vbworld.com/")
End Sub

'Brings up the print dialog box on button click.
Private Sub Command3_Click()
Dim eQuery As OLECMDF
eQuery = WebBrowser1.QueryStatusWB(OLECMDID_PRINT)
If Err.Number = 0 Then
If eQuery And OLECMDF_ENABLED Then
WebBrowser1.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_PROMPTUSER, "", ""
Else
End If
End If
End Sub

Private Sub cmdNavigate_Click()
Dim txtNavigate As String
WebBrowser1.Navigate txtNavigate
End Sub

Print Command In Vb6 To Print A Range In An Excel Worksheetsheet
Thank you for you assistance in populating an excel sheet from a vb6 user form textbox.

I am now struggling to print the excel sheet from vb6 command button.

I can print the worksheet but it is blank as I cannot work out how to set the print range.

Assistance would be appreciated as, after hours of trying, I am about ready to scream!!

Search By Worksheet And Range To Return Single Cell
Can anyone please help ? I don't know where to start.

I have to create a form (preferably web to share over an intranet) to search by worksheet name and then by x-range and y-range and give the single cell value as an answer. eg. Excel speadsheet no. 1 :

Syd Mel Per <- ex
Syd 1 2 3
Mel 4 5 6 <- dest
Per 7 8 9

The form should ask ex city and destination city. As above, if it is going from Mel to Per the it should return 8. The input fields should be dropdowns preferably filled from the range. There are a few spreadsheets that are the same, and the selection criteria for them is in the name of the sheet eg. 20%, 30%, 40% which is another dropdown on the form.

How do I do this? Should I use ASP.NET or XML and should I use the script editor for Excel XP or Visual Studio .NET?

Thanks.

Extracting Particular Range Of Values From A Worksheet And Save As A New .xls File
I have a Worksheet(pretty huge) and I want to extract a particular range of values to be extracted, say from BO:BX upto the row number say 50 into a new xls file. How do I do that? any help will be much appreciated.

a...

Print An .xls Worksheet From VB.net Program
I have a program that will issue reports (paystubs designed in excel) my program will process the data store relevant info into the excel work sheet and then print it (x2) then close the file without saving, open the file again put the data into the respected cells in the work sheet and print (x2) and close with out saving.

This will go threw and do this for each employee printing out two copies of the employees paystub.

I know how to get the info into excel, not sure how to get it in the exact cells i want but i am more concerned with the printing of the file, cause opening and closing is easy. thanks ado.net

Thanks for any help

Print Date On Excel Worksheet
Im fairly new to VB and i would like to know if there is a way to print the date (system date in my computer) in an excel worksheet, using a standard exe.

Also, i have also made a program in a standard exe that when you click the command botton, it automatically opens a couple of word documents, does a process, opens an excel workbook, and writes down the result of the process on the workbook, it then closes all documents and saves the changes on the workbook.
The problem now is that after closing the workbook, excel is still running inthe background. I've tried using "excel.application.quit", but it does not completely close excel. What should i do??

Print Worksheet If Still Present Within Workbook!
I’m a Vb newbie using Vb6 in Excel 2002 and tend to get by using modified codes to fit my needs so please bear with me.

At the onset, my workbook contains several worksheets; we’ll call them Monday thro to Sunday. Worksheets Saturday & Sunday will always remain in the workbook as base information. With the use of a UserForm and Checkboxes I have given the user the option to delete 1 or more of the weekday worksheets before another Sub runs and produces individual files for each weekday worksheets remaining. i.e. don’t do anything with wks Saturday & Sunday, but create a new individual file for wks Monday thro to Friday if they are still present.
What I’m having the difficulty with is, a routine that identifies if any of the remaining Monday thro to Friday worksheet are still present within the Workbook, and if so, print it out. Any day of the week could be missing.

Both “ActiveWorkbook.PrintOut” & “Sheets.PrintOut” - prints out all sheets
“ActiveSheet.PrintOut” – means I have to select the wks before printing it.

The code below is what I started with but I’m not sure if I’m on the right track ?

Sub Print_Remaining_Sheets()

msg = "Do you want a print out" & vbCrLf & _
" of each Weekday sheet?" & vbCrLf

result = MsgBox(msg, vbExclamation + vbYesNo, "Printing")
If result = vbYes Then
If wksSheet.Name = ("Monday") Then Print
If wksSheet.Name = ("Tuesday") Then Print
If wksSheet.Name = ("Wednesday") Then Print
If wksSheet.Name = ("Thursday") Then Print
If wksSheet.Name = ("Friday") Then Print

ElseIf result = vbNo Then
Exit Sub

End If

End Sub

Ideally I’d also like a msg box to reappear and ask if they want another copy.

Any help welcome.

VB Application Command To Print Excel Worksheet
I am most impressed with the assistance that I received yesturday. Thanks to all.

Now I have the vb application user forms populating the excel sheet and saving the sheet but, once again, I am stuck. I can print the sheet from a command button on the user form but it is blank as I cannot work out how to set the print range. At no time is the user to see the excel sheet. All they get to see is the final printout once they activate the "Print" button.

Once again your assistance would be appreciated. Thank you.

takowai

About Print Problem In Excel Worksheet That Was Been Referenced In Vb Project
I have a VB program that opens up an Excel sheet and prints it out. How can I access the printing preferences, options properties from VB so I can change them accordingly? and how can i set a state variable to determine whether or not that worksheet has been printed.

Assign Multiple Print Ranges Within A Single Excel Worksheet
Is this performed through a pagesetup function? Please get me started...thanks

Private Sub CommandButton1_Click()

Dim rCol As Range
Dim rCell1 As Range, rCell2 As Range
Dim Sht As Worksheet

'Set range variable to Selection
Set Sht = ActiveSheet
Set rCol = Sht.UsedRange.Columns(1)
'Insert page breaks
Sht.PageSetup.PrintArea =

How To Set Print Range
Hello all

I have made print preview. But it was large that A4 size. I am notgetting that how to set print range and if it overflow then it goes tonext page.

And how to give next page for print preview and printer

Thanks

Amrit

Print A Range Of Dates
I'm trying to pull appointment dates from a database and just print them into a picture box. How do I search for the dates? I have two input boxes: one with the start date and one with the end date. Those dates, and the dates between them need to print into the picture box. I have no clue how to go about this. Here's what I have so far:

Private Sub mnuAppointments_Click() 'on a drop down menu

Dim strReportDate1 As String, strReportDate2 As String, foundFlag As Boolean

strReportDate1 = UCase(InputBox("Enter the Start Date (MM/DD/YY):"))
strReportDate2 = UCase(InputBox("Enter the End Date (MM/DD/YY:"))

If Len(strReportDate1) > 0 Then
datContacts.Recordset.MoveFirst
foundFlag = False

Do While (Not foundFlag) And (Not datContacts.Recordset.EOF)
If Trim(UCase(datContacts.Recordset.Fields("Next Appointment").Value)) = strReportDate1 Then
foundFlag = True
Else
datContacts.Recordset.MoveNext
End If
Loop

If Not foundFlag Then
MsgBox "Please try again"
datContacts.Recordset.MoveLast
End If
Else
MsgBox "Please enter a start date"
End If

Dynamic Print Range
Hello to you

I need a subroutine that will look at a worksheet and identify the last cell that contains data and set the print area, up to and including that last cell. It must be renewable - i.e. it works every time new data is inputted onto the sheet.

Can anyone help me?????

Dynamic Print Range
Hello to you

I need a subroutine that will look at a worksheet and identify the last cell that contains data and set the print area up to and including that last cell. It must be renewable - i.e. it works every time new data is inputted onto the sheet.

Can anyone help me?????

Print Range In Excel
How can i set the print range in excel so that it will always print 1 page, if i use printArea it prints 2 pages instead of 1. Is it possible to always print to 1 page?

VB Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$R$55"

Clear Everything Outside Of Print Range
I am using the following sub routine in VB to clear all data in cells and columns outside a designated print range.
The problem is it only clears out up to Z. There are a few columns AA - AF that gets left. Why is this?

Cna you help?

-------------
Next shp

        If sht.Name <> "Wellhead Data Sheet" Then
        If sht.PageSetup.PrintArea <> "" Then
            lRight = Right(sht.PageSetup.PrintArea, _
              InStr(sht.PageSetup.PrintArea, ":"))
            uLeft = Left(sht.PageSetup.PrintArea, _
              InStr(sht.PageSetup.PrintArea, ":") - 1)
            Set rngClear = sht.Range(lRight).Offset(0, 1)
            rngClear.Select
            Range(Selection, Selection.End(xlToRight)).EntireColumn.Delete
        End If
        End If
        Range(uLeft).Select
        sht.Protect ("ciw")
---------------

Print Block Of Range
I have this sheet with 5 block of range.
How to make a macro and assing to the button in this mode:

Set range from A to P (last cell in this case cont last cell from A) and print a copy
Set range from R to AG (last cell in this case cont last cell from R) and print a copy
ecc...

Peraphs is a init:

Option Explicit
Sub ShowUsedRange()

Dim ELENCO As Worksheet
Dim RIGA_MAX As Integer
Dim COLONNA_MAX As Integer
Dim I As Integer
Set ELENCO = Sheets("STAT_NEW")
RIGA_MAX = ELENCO.Cells(65536, 1).End(xlUp).Row
COLONNA_MAX = 16
With ELENCO
For I = 1 To 5
ActiveSheet.PageSetup.PrintArea = "$A$1:" & Cells(RIGA_MAX, COLONNA_MAX).Address
'HERE THE COMMAND TO PRINT OUT AREA
COLONNA_MAX = COLONNA_MAX + 16
Next I
End With
End Sub

Select Print Range
I have multiple charts on one sheet. When printing i want to select both the charts to be selected..either print on one page or more than one page
Can anyone tell me how to do it through VB ?

-ash

Print Data Between A Date Range
I have a data base with a long list of enteries which I would like to beable to use a macro to print enteries between to dates. If having it print is to hard how do I get to copy the enteries between the dates to a new worksheet

Appending A Range To A Print File
Yikes.

Haven't been here for a while...

Seem to be going down a lot of dead end threads with this one.

Need to print various ranges to either the active printer &/OR append to a file.
To the active printer,
myrange.printout is working great
to append to a file i just seem to get more lost.
- does not seem to be a vba utility?
- shell(whatever) is getting me lost... no file
concatenate function in DOS?

help greatly appreciated.

* * * *.. i keep hitting tab here and, duh, of course going to the "Submit NEw Thread" button. Hope i haven't inadvertently submitted any incompletes or extras

thanks again..
kak5157

Use A Macro To Print An Area, But Range Is Always Different
Hi everyone,

I have a macro that formats some data for me. I use this macro very often and I would like it each time that it is run to create the right print range, so that I can immediately print of my sheet if I need to. The problem I have is that although I always have the same number of columns at the same width but there are always more or less rows than the last time I printed. How do I get VBA to recognise this and select the print area that has data only. Also, there are some rows in my sheet that have no data...is this a problem (they are in between data though).

Cheers

Max

Print Range In Excel From Vb Application
I have several vb user forms which populate an excel spreadsheet. I need to put a command button on a user form which will print out the excel sheet that has been populated. At no time will the user see the excel workbook.

I have managed to get as for as printing the sheet but the print-out is blank as I have been unable to work out how to tell the printer what the print range is.

I realise that this must be a very basic question but I would appreciate some help.

Setting A Range Equal To That Of The Print Area
I have found code that will allow me to attach a certain range, on an excel sheet, into an outlook e-mail and then send it.

However, it requires you to select the range.

The range I want sent, happens to be the same range as the printable area.

Is there a way to just have the rangeToSend = printArea?

VBA Fill Formulas In Worksheets And Print Range
:huh: Hiya

Maybe you can help me with this one

Do you know what code I would need to fill down formulaes in subsequent worksheets depending on figures in a master sheet. Say Range("a3").Value

For example if the master sheet had 1 apple, 2 oranges and 3 tomatoes, how could it recognise the number of each and copy the last row of formulas according to the number?

I would then require the code to copy down the last row of formulas twice in the oranges worksheet and three times in the tomatoes. Basically it needs to be smart enough to formulas according to the number inserted in the first sheet.

Finally I need the macro to print say the last twenty rows of formulas in each worksheet ( a set print area which will encompass the new formulas that have been copied down).

It sounds easy but I think it is much more difficult

Here is the code I am using for the copy fill
Code:
Range("F24:G24").Select
Selection.Copy
Sheets("Macq True Index FI- FIFO").Select
Range("A130").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Data Sheet 106").Select
Range("C24:D24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Macq True Index FI- FIFO").Select
Range("A131").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C129:H129").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C129:H131")
Range("C129:H131").Select
Range("H130").Select
Sheets("Macq True Index FI- FIFO").Select
Range("A130:A131").Select
Selection.Copy
Sheets("Macq True Index FI (Gains)").Select
Rows("135:135").Select
Application.CutCopyMode = False
Selection.Copy
Rows("136:137").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B137").Select
Selection.ClearContents
Range("N136").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-12]=0,0,IF(RC[-7]-RC[-6]-RC[-5]=0,((RC[-12]-R[-1]C[-12])*RC[-4])," & _
"((RC[-7]*(RC[-12]-RC[-11]))+(RC[-6]*(RC[-11]-RC[-12]))+(RC[-5]*(RC[-10]-R[-1]C[-12])))))"
Range("N137").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("B137").Select
ActiveCell.FormulaR1C1 = "1.001"
Range("B138").Select
End Sub

Common Dialog/PrintDialog API Print Range
All,
I have exhaustively searched this website as well as other to find how to activate the Current Page or the Pages radio options on either the commondialog control or the PrintDialog API. If anyone could point me in the right direction that would be helpful.

I have been setting the .Flags to cdlPDPageNums, in fact, I have tried all available flags in some combination. Maybe there is a trick to an And/or clause in the setting of the flag that I am just not getting correct.

Thanks in advance

VBA Excel: Combine Print Range On Same Page
Assign multiple print ranges within a single Excel Worksheet

Is this performed through a pagesetup function? Please get me started...thanks

Private Sub CommandButton1_Click()

Dim rCol As Range
Dim rCell1 As Range, rCell2 As Range
Dim Sht As Worksheet

'Set range variable to Selection
Set Sht = ActiveSheet
Set rCol = Sht.UsedRange.Columns(1)
'Insert page breaks
Sht.PageSetup.PrintArea =

Delete Worksheet, Then Create New Blank Worksheet With Same Name
Hi

I have a worksheet named "Harmonics", where I present to the user the results from my VB program.

But when the user re-runs the macro, I want to ensure that ALL the data on the worksheet is overwritten and no 'old' data is still there.

I thought the best way to do this would be to delete the entire worksheet at the start of the program then re-create the worksheet as a blank document ready for writing to. Is this a bit extreme? Would I be better to just delete all cells within the worksheet as opposed to the sheet itself?

Your help would be most appreciated.

And of course, some code to do what I want would be great!

Thanks,
Daryll

Separate Data In Worksheet Cells Then Add To New Worksheet
I have a spreadsheet with 9 columns and a variable number of rows. Each of the cells contains multiple values which include a date, time and location. I want to populate a second spreadsheet the the data from each of these cells. I have attached an example workbook which has the original data in SheetA, and the results I am trying to achieve in SheetB. The spreadsheed will be easier to understand than my explanation I think.

The data in the cells needs to be separated into individual cells in the new sheet, for each set of cell data, the associated data in columns 1 and 9 need to be added/repeated.

Is this possible?if yes, how do I achieve this?

How To Check If A Worksheet Exists In Worksheet Collection
Hi All,
I wanted to know if there is any method to check if a worksheet exists in worksheets collection.I want to check this by worksheet name.

Thanks in advance

Regards
Raghu

Changing Worksheet Name When Copying Worksheet
Hi guys I have an Excel problem. I am having a worksheet and i duplicate it when i need to have it more times. Therefore I use the following code:

Code:
ActiveWorkbook.Sheets("Template_Sheet").Copy After:=ActiveWorkbook.Sheets(1)
When I copy the "Template_Sheet" Worksheet I get one with the name "Template_Sheet (2)"
How can i make the new copied Worksheet have the name I choose for it?

Thanx for any advice

To Determine The Rows To Paste A Worksheet(blank Cells To Paste Next Worksheet)
hi all experts out there....

would llike u guys to help out with a problem i am facing....

the code below is to select each of the worksheet,copy it and paste it in worksheet (printer").....i got 3 worksheets to paste into worksheet ("printer")....worksheet("11 aug"),("12 aug") and ("18 aug")....i got no problem pasting the first two worksheets("11 aug") and ("12 aug")...but i got a problem pasting the last worksheet("18 aug").....i want to paste each worksheet with a offset of (3,0) but the last worksheet paste at another cell instead......i attach the excel doc name (total)(workbook) and the macro.....the ("sub regroup")....need to be improve..pls help ......thank a lot..."P

Code:
Sub regroup()
'
' regroup Macro
' Macro recorded 9/3/2003 by SGTANCKE
'

'
Sheets("11 Aug").Select
Rows("1:" & Sheet1.UsedRange.Rows.Count).Select
Selection.Copy
Sheets("Printer").Select
Range("A1").Select
ActiveSheet.Paste

Sheets("12 AUG").Select
Rows("1:" & Sheet2.UsedRange.Rows.Count).Select
Selection.Copy

Worksheets("Printer").Activate
ActiveCell.Offset(rowOffset:=3 + Sheets("Printer").UsedRange.Rows.Count, columnOffset:=0).Activate
ActiveSheet.Paste

Sheets("18 AUG").Select
Rows("1:" & Sheet3.UsedRange.Rows.Count).Select
Selection.Copy

Worksheets("Printer").Activate
ActiveCell.Offset(rowOffset:=3 + Sheets("Printer").UsedRange.Rows.Count, columnOffset:=0).Activate
ActiveSheet.Paste

Columns("E:E").EntireColumn.AutoFit

End Sub

Loop Through Named Range And Lock Range Name Based On Values
ok..basicallly I created a worksheet called 'calendar'
this calendar worksheet has 12 named ranges

rngJanuary,rngFebrary....to rngDecember(yes its a calendar)

all of these named ranges are also inside one big range called rngcalendar.

Basically I want to accomplish the following.

when the workbook get opened get the last day of the last month the user entered a value and lock every other named range after the month of the named range the last value was stored in.

eg..if on march 20th the user entered a "w" as a value I want to lock
RngApril through rngDec.

in other words force them to finish entering all values for a month before continuing to the next.

if IT helps all my days of the month are in ltgrey coloring
and the vales the enter go below the day value
in normal white cells(see attachment)

I already validate the entried in the
Private Sub Worksheet_Change(ByVal Target As Range) event but dont know how to accomplish the above step.

Excel Range In Variant Array, 'Subscript Out Of Range' Error
Morning Guru's

I have been having problems with this function for a few days now. I'd be most grateful if one of you could set me straight with this. Here is my code:

Code:

Private Function RemoveNulls(ByVal srcRange As Excel.Range, ByVal lastRow As Long)
MsgBox srcRange.Address

Dim i As Long
Dim xlVals() As Variant
xlVals = srcRange.Value
For i = LBound(xlVals) To UBound(xlVals)
If xlVals(i) = -9999 Then xlVals(i) = 0
Next i
srcRange.Value = xlVals
Set srcRange = Nothing
End Function

The compiller gives me the 'Subscript out of Range' error on the line:
If xlVals(i)=-9999 Then xlVals(i)=0

In-fact if I try to get a msgbox or something to display any value in the array, I get the same error. The Lbound and UBound functions are returning the expected values, therefore I believe that the array definately contains data!?

Any Ideas Amigo's?

Thanks

Copying Dynamic Range Based On Cell Outside Of Range
i am copying a dynamic range of cells from twelve different worksheets. i need to select a range that starts of g10 on every sheet, but ends differently on every sheet. the range ends depending on column A in the last row of each range, the word 'total'. i would like to select the range in column g10 down to the cell corresponding with 'total' in column a in column g. For example, if there is 'total' is in a56, i want to select and copy g10-g56. If on another sheet 'total' is in a68, i want to select and copy g10-g68. there is information below the range. i want to ignore the data below altogther. here is the code that i have now for the first sheet. it grabs G10 through the last cell in column g. any help would be appreciated. thanks

Code:
Sheets("January 2005").Select
Range(Range("G10"), Cells(Rows.Count, Columns("G").Column).End(xlUp)) _
Copy Worksheets("SALES REPORT").Range("a2")

Determining Wether Current Cell/range Within A Specified Range
Hi,
I am trying to write a macro for excel spreadsheet.
I am trying to find some function or method to determine wether a particular cell is currently within the specified range or not.

A simple example to illustrate question:

Dim Action_Range as Range
Set Action_Range = Range("A1:H:10")

Dim Current_Range as Range
Set Current_Range = ActiveCell

If Current_Range is part of the Action Range then
'do something
Else
'ignore
End if

Can somebody give some suggestions?
Thanks

Excel Macro, Subscript Out Of Range, But Its In Range?
i have a 2d array:
wds(1 to 24, 0 to 1)
the values in the array are set up correctly, but when i try to
output the values of the array to a cell i get the subscript out of
range error.

Below is the code that ive used to write to the cells, to test
that the array has the correct values the commented out
msgbox was used. (it returned the correct values)
except when i use the same code to assign the value to a cell's value i getthe error.

Code:

dim count1 as integer
count1 = 4
For counter2 = 1 To 24
'MsgBox (wds(counter2, 0))
Sheets("Doubles Results").Range("C" & count1) = wds(counter2, 0)
Sheets("Doubles Results").Range("E" & count1) = wds(counter2, 1)
count1 = count1 + 1
Next counter2

thanks for any replies

ps.
with other languages ive used to do simple calculations like
variable += 3 to add 3 to the variable
variable ++ to increment the variable
is there something similar to this in VB?

Excel: Named Range Will Not Accept New Range Value
I have 2 worksheets in the same workbook
On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32"
This range value changes due to specific triggers which aren't important
In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3"
On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet
So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to
When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range
I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window)
But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all

The call to the function looks like this:
Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")

The actual function looks like this (I have placed the actual values in comments at the end of the line so you can follow what is happening):

Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _
                                ObjWkSheetName As String, InfoType As String, _
                                IRNumber As String, RangeInfoCell As String)

Dim myexcel As Object
Dim myworkbook As Object
Dim sourceworksheet As Object
Dim objworksheet As Object
Dim PivotTableName As String
Dim PivotSourceData As String
Dim NameRef As String

    Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
    Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
    Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
    Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet

    PivotTableName = "PIR" & IRNumber & InfoType    ' PIR1DTCAT

    If sourceworksheet.Range(RangeInfoCell).Value = "None" Then    ' C4:L33
        'There is no downtime data, hence the pivot table will not be updated
    Else
        PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value    ' 'PIR-DT MTH'!C4:L33
        NameRef = "PIR" & IRNumber & "DB"    ' PIR1DB
        myworkbook.Names.Add NameRef, PivotSourceData    'This is the line which doesn't work
        objworksheet.PivotTables(PivotTableName).PivotCache.Refresh

        myworkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False
    End If

End Sub

Changing the Named range doesn't work
The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38"
I have no clue where it gets this from
And everytime you run the code, the strange range changes
Even when I hardcode the value (see below) , it still doesn't work
myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33"

This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere
Am I using any predefined VB words?
Is the sky falling?

Searching A Range And Copying Values Into Another Range
Hi all.

Can someone help me with a problem I have. Working in Excel, I have a range (B11:E15) that looks like:

Date    Consumption    Limit    Excess
01/01/04    1100        1000    100
02/01/04    980        1000    0
03/01/04    760        1000    0
04/01/04    1200        1000    200

What I need is code that will search through the column called 'Excess', and for any values that are greater than 0, copy the entire row (values only, not formatting, or equations that may be behind the scenes) into a new range starting at G12. The code will search each row of the data and for any other rows that have an 'excess', VB will paste it immediately beneath any existing entires in the new range. What I want to be left with is:

Date    Consumption    Limit    Excess
01/01/04    1100        1000    100
04/01/04    1200        1000    200

I hope that anyone can help with this as im brand new to VB. I've been searching many forums for people asking similar questions, but i'm having a hard time of it.

Many thanks to anyone that can help.

Looping A Range Format Identical Range.
Hi Guys and Gals,

Ive been working on this project and needed a simlpe loop though a set range and check for 5 different values, if the values are present format a cell with the identical value on another sheet.

Code:
Sub LeaveTravelOfficeSick()
    Sheets("Leave-Travel-Office-Sick").Select
    Dim i As Integer
    Dim rng As Range, rng2 As Range, rng3 As Range
    Dim rngAll As Range, rngall2 As Range, rngall3 As Range
    'lngNumRows = Columns("Y:BA").CurrentRegion.Rows.Count - 1
    'Set rngAll = Range("N3:R" & lngNumRows)
    Set rngAll = Range("E6:E10000")
    'Set rngall2 = Range("F6:F10000")
    'Set rngall3 = Range("G6:G10000")
    j = 6

    For Each rng In rngAll
        With rng
            'If Not IsError(.Value) Then
            Select Case .Value
                    Case "1/2 Leave"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "1/2 LEAVE" ' displays name OFFICE as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Leave"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "LEAVE" ' displays name LEAVE as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Sick"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "SICK" ' displays name SICK as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Office"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "OFFICE" ' displays name OFFICE as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
                    Case "Travel"
                       With Worksheets("crew").Cells(j, 17)
                        .Value = "TRAVEL" ' displays name TRAVEL as cell value
                        .Interior.ColorIndex = 1 ' colours cell black
                        .Font.ColorIndex = 27 'colours text yellow
                       End With
            End Select
        End With
    j = j + 1
    Next rng
End Sub

I built this sub above, it loops through every cell on sheet 1 column E starting at cell E6 and stopping E10000.

It then formats the corresponding cell on sheet 2.

Sheet 1

date jim andy simon
01/01/2007 sick travel sick
02/01/2007 sick sick sick

Sheet 2

date jim andy simon
01/01/2007
02/01/2007

So when the loop finds sick in 01/01/2007 for jim, it then formats the same cell on sheet 2 with sick.

The above sub only does 1 name, to do muliple names i do the same For Each rng In rngAll but just increase the number by 1 and the range value to the next column.

Is there a way to do it with code to save me recreating the loop for 40 more employees?

Any help would be great, will save alot of copy/paste and find/repace values

Al

Edited by - Big Al Inc on 6/19/2007 7:11:07 AM

RESOLVED: Finding A 'range' From Within A 'range'? - Excel VBA
Relative newbie...

Wanting to get coding that will perform a search through a spreasheet range to find a unique row of data (i.e. a match to the contents of first 3 columns), returning the row No. where the match is found. I had thought that a 'range.find' would accomplish this, but have since been advised otherwise.

Anyone any ideas????

Adjacent columns in the row are for additional pertinent data. The search will be used either to retreive the additional data, or, to add to or change it.

thnx in advance,
remalay

Edited by - Remalay on 8/22/2006 6:50:32 AM

Copy From Worksheet To Worksheet??
I need to copy info from one cell in worksheet1 to another cell in worksheet2. The problem is I have a formulated cell in worksheet1 and when I copy from worksheet1 to worksheet2, it copies the formula. I need to copy the value only. Here's the code I'm using.

Worksheets("BT WORKSHEET").Range("A60").Copy
Worksheets("REQ.").Select
Worksheets("REQ.").Range("B11").Select
Worksheets("REQ.").Paste

Thanks for any help.

To Copy Worksheet At The End Of Other Worksheet
I WANT TO KNOW HOW TO COPY THE EXCEL WORKSHEET AT THE END OF OTHER WORKBOOK SHEET.
I HAVE WRITTEN CODE IN VB6.0 BUT IT OVERWRITE THE WORKSHEET WHILE COPYING.

Private Sub File1_Click()
Dim FileToCopy As String: FileToCopy = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLSMASTER_FILECAP 8 MASTER FOR RS232 V.95.xls"
Dim NewFile As String: NewFile = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLS2005" + File1.FileName
FileCopy FileToCopy, NewFile
Exit Sub
End Sub

Private Sub Form_Load()
Me.Show
Me.Caption = "Copying..."
File1.Path = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLS2005"

End Sub

Excel Named Range --> Vb Range
Hello,

I have an excel spreadsheet which has a named ranged which defines a table.

in vb.net i want to open the workbook goto the sheet (this i can do) then copy the table using the named excel range stored in the worksheet and then paste this table into powerpoint.

I do not know how to refrence the named range in excel. is it part of the worksheet property? i have not been able to figure that out.

i also might have problem with the pasting into powerpoint. when i paste the data will the grid from excel show up or can i paste it in as a picture?

Thanks

Copy A Range Beneath Another Range
I have two columns with values. I want to copy a range beneath another range how can i do that. I shall explain it with an example. It must be in a macro...

Column A-----Column B-----
10------------- 50
20------------- 60
30------------- 70
40------------- 80

what i want is :

Column A---
10
20
30
40
50
60
70
80

The problem is when i use

....
Selection.End(xlDown).Select
.paste
....

then i past the range in value 40 in column A. I must copy BENEATH column A, how can i realise this

Something like
Selection.End(xlDown+1).Select ??

Thanks in advance

Range Selection: Unknown Range
I'm trying to find a way to select a range based on other values. Range() uses exact cell names (A1 etc). But since the # of rows can vary, exact cell names won't always be valid for any amount of data.

for example,
A7 will always be used, but the last part of the range can vary depending on the # of rows present.

i'm using a user defined function LastRowInColumn() to return the # of the last row present, and i'm using another function to return the correct column #
example
x=LastRowInColumn(2)
y=column#

The problem i'm having is using those 'x' and 'y' variables

What i would need it do to is something like this:

Range("A7:yx").select <--- where y and x are the variables above. Of course this doesnt work because Range() expects exact cell names. I've tried messing with the Intersect function but still the same problems. Any ideas?? Thanks