Need some help on the best way to do something

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
We create an estimating sheet to cost out jobs. It then goes to the next person where a proposal is made.

When the estimating sheet is created, we copy it to a floppy 1.44mb and print a hard copy. It goes to the desk of the person that does proposals.

The person that does the proposal, puts the disk in and much of the info is then transfered to the Proposal. Like Client, Address, Phone, Fax Num., Floor Type, etc. For 2 reasons we do this: 1) it saves typing 2) It keeps from typos! I have a button on the proposal that retrieves the file name "Estimating" & the data from Floppy a: puts it on sheet 1 on the Proposal.

Floppies are just not reliable. The disks half the time do not work so we are retyping the data. I want a new and easier way!!

Anyway!!

We are set up on a network. I would like to click a button and have the data transfer if possible. Or once the Estimmating sheet is done, transfer the data. The computer where proposals are done, is always on. The computer for estimating is not always on.

What is my best approach?? It now searches Floppy A: for the same file name each time. If we write a new proposal, we just put a new disk with the same name. We would not be able to do this now?? We can't save to the same directory, a file with the same name??

Any help would be greatly appreciated!!!!!!!
Michael :cry: :(
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Why not create a template workbook? A template can have links to a hidden data file.

If it's on a network, you can share it and set each user's access rights to the folder.

You may also want to look into a shared workbook.

Hope that helps,

Smitty
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

I thought about copying the data from the estimating sheet to a "New Workbook" just with data. Then get the data I want from the Proposal when I need it.

Let me ask this, will the Workbook need to be open to send data to it or to get data from it??

Michael
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Let me ask this, will the Workbook need to be open to send data to it or to get data from it??
That depends on how you're trying to get the data. If it's VLOOKUPs then you can reference a closed workbook. Other methods may require opening the workbook, which you can do in the background programmatically.

Smitty
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

I am close on this!!! :)
I can have the Estimator (<--spelling??) change the name of each Estimate then... Copy the file to a directory on the Proposal persons computer. No problem!!

Now, is there a way, when the person doing the proposal can Open The estimating sheet that needs done... go to Cell a1 in the Estimating Sheet.... Hit a button on the Proposal Sheet that would copy just "Sheet1" from the estimating sheet that's open and... paste it over to the Proposals 'Sheet1" starting at Cell A1

Not too strong on code!! Any help with the code that will do what I am saying, would be fantastic.

Michael
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Now, is there a way, when the person doing the proposal can Open The estimating sheet that needs done... go to Cell a1 in the Estimating Sheet.... Hit a button on the Proposal Sheet that would copy just "Sheet1" from the estimating sheet that's open and... paste it over to the Proposals 'Sheet1" starting at Cell A1
Sure, and that's easily recordable. A consideration would be if the path will change from user to user though. Post the code after you're done though, so we can get a chance to clean it up for you.

Smitty

(You spelled it right :wink: )
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Here is what I have now:

Code:
Sub MoveDataFromA()
'
' MoveDataFromA Macro
' Moves data from a drive
'
    ChDir "A:\"
    Workbooks.Open Filename:="A:\Estimating Sheet and R17 Fill.xls"
    Sheets("ESTIMATING").Select
    ActiveWindow.SmallScroll Down:=-45
    Range("D2:L63").Select
    Selection.Copy
    Windows("A Proposal for XL.xls").Activate
    Sheets("Estimate from A-Drive").Select
    ActiveWindow.SmallScroll Down:=-9
    Windows("Estimating Sheet and R17 Fill.xls").Activate
    ActiveWindow.SmallScroll Down:=-66
    ActiveWindow.SmallScroll ToRight:=-4
    Windows("A Proposal for XL.xls").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("FRONT").Select
    Range("N2").Select
End Sub

Right now I hit a button and the macro is executed.

1) I want to change the Directory to "C:\WINDOWS\Personal\Surface Systems\Estimating" where the Estimating sheet was saved.
2) I would like an input box or a way to enter the name of the estimating sheet I want to get data from.
3) Then mostly the same copy D2:L63 into the "Estimate from A-Drive" (Which is Sheet1)

Thank You for any help you can give,
Michael
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top