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

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

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

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