How can I create a Macro that can update a sheet?
Here's an example that will get you started. I've used Excel's build in
GetOpenFileName function to allow the user to select the desired source data
sheet. There is a separate sub routine that does all the work - "LoadData".
This takes two parameters- the fulll path of the excel file to use as the
data source, and a worksheet object to copy the data to. This sub routine can
then be called from a main sub e.g. Demo_LoadData.
Sub LoadData(strSourceFilePath As String, wsDest As Worksheet)
'''Open file and copy data from file to wsDest
Dim wsSource As Worksheet
'speed things up and hide process from user
Application.ScreenUpdating = False
'open source file and set object variable to first worksheet in workbook
Set wsSource = Application.Workbooks.Open(strSourceFilePath).Work sheets(1)
'copy/duplicate data from Source sheet to Dest sheet
wsDest.Range("A2").Formula = wsSource.Range("B10").Formula
wsDest.Range("C5").Formula = wsSource.Range("D2").Formula
'etc...
'tidy up
wsSource.Parent.Close savechanges:=False
End Sub
Sub Demo_LoadData()
Dim strS As String
'prompt user to select an Excel file
strS = Application.GetOpenFilename( _
filefilter:="Excel files (*.xls),*.xls", _
FilterIndex:=1, Title:="Select data file to load:")
'call sub to open file and load data to active worksheet
LoadData strS, ActiveSheet
End Sub
Cheers,
Dave
"cimbom" wrote:
Hi all,
I am trying to create a macro that can update the entire sheet. I have
this data entry sheet and I also have a data.xls file that is converted
from a text file. This data.xls file changes every month. So, instead
of typing all those numbers every month into the data entry sheet,
which is a different workbook, I want to create such a macro that when
I run it, it should load all the numbers for the new month into the
data entry sheet.
If I make a user form in Macro and add a button and a text box to it,
what would the overall code be?
This way, if the user wants to see a certain month, he types it into
the text box and clicks on the button and the sheet will load the
numbers of that month.
You can email me
I would be so happy if you guys help me out. Thank you !
|