Loading Values From Another Sheet Automatically

[SanctifiedRock]

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

[Ron de Bruin]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Tom Ogilvy]

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
>
>

[SanctifiedRock]

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 without opening the other workbook, it just automatically pulls certain cells from the workbook while it is closed and displays them in the new workbook?