[ExcelVBA] Re: Using cell values from one sheet as workbook and spreadsheet nam

 

Laerte/Group,

I do a lot of this sort of thing using the following simple technique which hopefully you can adapt.
Hope this helps.

Regards
Phil.

SSub FullSequence
'
' Load Data from Files
'
ThisFile = ActiveWorkbook.Name

'Assume your filenames(Column C) and paths (Column D) are in rows 7 to 15 in a sheet called "Parameters"

For I = 7 To 15
'Read Parameters
AnalysisFile = Worksheets("Parameters").Range("C" & I).Text
AnalysisPath = Worksheets("Parameters").Range("D" & I).Text
'You could also read in the Sheet name and ranges for copying as variables if you wish

'Load Analysis File
Mywin = AnalysisFile
Myfile = AnalysisPath & Mywin
Call Loadwbook (Mywin, Myfile)

'Copy Data
Workbooks(AnalysisFile).Worksheets("DataSheetname").Range("A1").Copy
Workbooks(ThisFile).Worksheets("Data").Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Workbooks(AnalysisFile).close

Next I

End Sub

Sub Loadwbook(Mywin, Myfile)
' Macro by ransph01 December 10, 2009

' Check for files and load if required
f = 0
For Each W In Workbooks
x = W.Name
If x = Mywin Then f = 1
Next W

If f = 0 Then
' MsgBox "Expected Source File" & Mywin & " Not Loaded, Opening File"
Workbooks.Open Filename:=Myfile, UpdateLinks:=0
End If

End Sub

[Non-text portions of this message have been removed]

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 5

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___