Copy cell data from workbook based on user input

[Guest]

Hello All, any help would be greatly appreciated. Im trying to copy the cell
data from one workbook to another and Im not sure what Im doing. Could
someone please help? I want the user to type "30" and it to find the data on
30.xls .. etc.

Here is what I have.. obviously it dosn't work

Sub Copy()
'

Dim sStuff As String


sStuff = InputBox("What date is this for? I.E 1,2,3 etc.")


'
Range("B20").Select
ActiveCell.FormulaR1C1 = "='\\Server1\Important Files\[" & sStuff &
".xls]Totals'!$H$3"

End Sub

Thanks in advance!

 

[evgny]

Hi Michael A
try this in your sub after inputbox

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\Server1\Important Files\" & sStuff & ".xls"
Worksheets("your sheets name").Activate
Range("B20") = Worksheets("your sheets
name").Range("H3").Value
ActiveWorkbook.Close False
Application.ScreenUpdating = True

regards yngve

 

[Guest]

Hi Evgny,

Thanks for the response. This is close to what I need. However it is trying
to put the totals of cell H3 into B20 on the same sheet. I need it to take
the info from the cell H3 on the sheet that was opened to the B20 column on
the sheet that ran the macro. Any idea on how I can do this?

 

[evgny]

Hi
range(h3) is that on the workbook you open or .....
and there are only one value i range(h3), what do you meen with
"Totals"

regards yngve

 

[Guest]

Hi,

In totals, I ment Values. I want the value in H3 on the worksheet in the
workbook that is opened by your macro to be put into the B20 cell of the
original sheet in the original workbook. (the sheet the macro was opened
in)by the user.

 

[evgny]

hi Michael

Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"\\Server1\Important Files\" & sStuff & ".xls"
Worksheets("your sheets name on the sStuff").Activate

' now you are in the workbook(sStuff.xls).worksheets(????)

workbooks"(your original workbook name"). Worksheets("your
original sheetsname"). Range("B20") = workbooks(sStuff &".xls").
Worksheets("your sheets
name in sstuff").Range("H3").Value
ActiveWorkbook.Close False
Application.ScreenUpdating = True

Regards
yngve

 

[Guest]

Evgny,

That worked! Thank you so much. You have been such a great help.

- Mike

 

[evgny]

You are welcom, happy new year.

Regads Yngve