Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Values From Another Sheet Automatically
I've created a sheet to estimate a job project and now I am working on a new sheet to keep track of our estimated totals compared with actual expenses. What I need is to make a macro that allows you to browse the PC and let the user select the excel sheet where all the Estimate Values are stored. Afterwhich, the main sheet would pull values from that sheet. {Use Cell A1 as an example and I can take it from there} Thanks -- SanctifiedRock ------------------------------------------------------------------------ SanctifiedRock's Profile: http://www.excelforum.com/member.php...o&userid=34485 View this thread: http://www.excelforum.com/showthread...hreadid=560804 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Values From Another Sheet Automatically
Hi SanctifiedRock
You can try http://www.rondebruin.nl/ado.htm Or open the file like this to copy A1 of the first sheet to the workbook with this macro Sub test() Dim FName As Variant Dim wb As Workbook Dim MyPath As String Dim SaveDriveDir As String SaveDriveDir = CurDir MyPath = ThisWorkbook.Path ChDrive MyPath ChDir MyPath FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls") If FName < False Then Set wb = Workbooks.Open(FName) ThisWorkbook.Sheets("Sheet1").Range("A1").Value = wb.Sheets("Sheet1").Range("A1").Value wb.Close False End If ChDrive SaveDriveDir ChDir SaveDriveDir End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "SanctifiedRock" <SanctifiedRock.2auh36_1152728108.9853@excelforu m-nospam.com wrote in message news:SanctifiedRock.2auh36_1152728108.9853@excelfo rum-nospam.com... I've created a sheet to estimate a job project and now I am working on a new sheet to keep track of our estimated totals compared with actual expenses. What I need is to make a macro that allows you to browse the PC and let the user select the excel sheet where all the Estimate Values are stored. Afterwhich, the main sheet would pull values from that sheet. {Use Cell A1 as an example and I can take it from there} Thanks -- SanctifiedRock ------------------------------------------------------------------------ SanctifiedRock's Profile: http://www.excelforum.com/member.php...o&userid=34485 View this thread: http://www.excelforum.com/showthread...hreadid=560804 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Values From Another Sheet Automatically
this pseudo code might help:
Dim fname as String Dim bk as Workbook Dim rng as Range fname = Application.GetOpenFileName( _ FileFilter:="Excel Files (*.xls),*.xls") if fname = "False" then ' user hit cancel, exit sub exit sub end if set bk = workbooks.Open(fname) set rng = bk.Worksheets(1).Range("A1") rng.CurrentRegion.copy _ thisworkbook.worksheets(1).Cells(rows.count,1).End (xlup)(2) .. . . bk.close SaveChanges:=False -- Regards, Tom Ogilvy "SanctifiedRock" wrote: I've created a sheet to estimate a job project and now I am working on a new sheet to keep track of our estimated totals compared with actual expenses. What I need is to make a macro that allows you to browse the PC and let the user select the excel sheet where all the Estimate Values are stored. Afterwhich, the main sheet would pull values from that sheet. {Use Cell A1 as an example and I can take it from there} Thanks -- SanctifiedRock ------------------------------------------------------------------------ SanctifiedRock's Profile: http://www.excelforum.com/member.php...o&userid=34485 View this thread: http://www.excelforum.com/showthread...hreadid=560804 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loading Values From Another Sheet Automatically
this pseudo code might help: Dim fname as String Dim bk as Workbook Dim rng as Range fname = Application.GetOpenFileName( _ FileFilter:="Excel Files (*.xls),*.xls") if fname = "False" then ' user hit cancel, exit sub exit sub end if set bk = workbooks.Open(fname) set rng = bk.Worksheets(1).Range("A1") rng.CurrentRegion.copy _ thisworkbook.worksheets(1).Cells(rows.count,1).End (xlup)(2) .. . . bk.close SaveChanges:=False -- Regards, Tom Ogilvy Can I get this written in a way where it draws out the data withou opening the other workbook, it just automatically pulls certain cell from the workbook while it is closed and displays them in the ne workbook -- SanctifiedRoc ----------------------------------------------------------------------- SanctifiedRock's Profile: http://www.excelforum.com/member.php...fo&userid=3448 View this thread: http://www.excelforum.com/showthread.php?threadid=56080 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i stop the reviewing toolbar from automatically loading? | Excel Discussion (Misc queries) | |||
update a sheet automatically with changing values from another she | Excel Worksheet Functions | |||
How do I automatically copy cell values from one sheet to another? | Excel Programming | |||
inserting values automatically from one sheet to another | Excel Programming | |||
Loading values into a form on open. | Excel Programming |