macro to browse for workbook, pick up data and looping

[uriel78]

I've got several workbooks; in each workbook there are 4 sheets whose name
is the same for ale the workbooks (call'em sheet1,2,3,4).
In each sheets there are equally organized data (only values change form one
sheet to another)

In a new workbook ("summary") I wish I could have a macro that ask me to
choose a workbook (maybe without opening it), pick up and copy data in
sheet1 and paste in summary workbook. After doing this operation, I'd qant
to be prompt if select another file to do the same operation (choose
workbook, sheet, data ,copy& paste)...

Is it possible...?

 

[Tom Ogilvy]

application.GetOpenfileName

provides the file open dialog to select a file.

it returns a fully qualified file name which can be used to open the file
and copy the data.

You can put this in a loop and when the user clicks cancel, you can stop the
loop

 

[Ron de Bruin]

This is a start
http://www.rondebruin.nl/summary2.htm

If you need more help post back

 

[uriel78]

thanks to both of you I will try with your suggestions

 

[uriel78]

Two little questions for you (sorry if the solution is simple but I'm going
to use VBA for only few days...)
COnsidering the procedure describe for "copy a range from a closed workbook"
:

1)How can I use it to copy data from two files (1by1 select) and put the
data form file2 starting in columns C (actully, after choosing file2 data,
they overwrite previous data starting in column A)

2) "Copy a range from all files that you have selected with GetOpenFilename"
: how can I define a procedure to substitute the LastRow function with a
"LastColumn" Function...?

3)Last & least: most of data I need to import in a summmary workbook consist
of two columns of data (X and Ycoordinates), i.e. I:L columns. So, by using
your routines (maybe modified with the tips just asked at point 1 and 2) I
can import I:J columns from different workbooks in columns A:B (for the
first file), D:E for the second file and so on.
How can I do to put in cells A1, D1 (in general, the first cell) the name of
worksheet and workbook the series come from...?

The final result should be sthg as

Column A Column B
Column D Column E
workbook1 - worksheet 1
workbook2 - worksheet 1
X1 Y1
X1 Y1
X2 Y2
X2 Y2

and so on...

I hope you can help me to solve these questions...it will be very important
for me....

 

[uriel78]

Sorry, the table posted for example is badly-formatted....:-(

 

[Ron de Bruin]

to use VBA for only few days...)
You want to much I think<g>

Use this code example instead
http://www.rondebruin.nl/copy3.htm#column
You don't have to use function for this

You can use mybook.name to fill a cell with the workbook name

You can use this udf in the range example if you want

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

 

[uriel78]

Thank you very much for your kindness and rapidity, I'll try and then tell
you the results!!HAve a good night!!

 

[uriel78]

Just another little question,
If I want to import data from 4 sheets (by specifying their names) (not
every sheet) how can I modify the macro...?

I don't know how...I suppose I've to change sthg in

GetData FName(N), "sheet1", "I:J", destrange, False


I need to import columns I:J from 4 worksheets for each workbook....can you
help me...?

 

[Ron de Bruin]

Hi uriel78

Use Dir for this.
Easier, Copy the code in a workbook outside the folder C:\Data.
It will use the first sheet of this workbook

Change

For Each sh In mybook.Sheets(Array("Sheet1", "Sheet3"))

MyPath = "C:\Data"




Sub Tester()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim Colnum As Long
Dim SourceCcount As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim sh As Worksheet

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
Colnum = 1

Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
For Each sh In mybook.Sheets(Array("Sheet1", "Sheet3"))
Set sourceRange = sh.Columns("I:J")
SourceCcount = sourceRange.Columns.Count
Set destrange = basebook.Worksheets(1).Columns(Colnum)

sourceRange.Copy destrange
Colnum = Colnum + SourceCcount
Next sh
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[uriel78]

thank you, I forgot to say I wish I could use the

FName = Application.GetOpenFilename

instead of dir (that, as I can see, selects every file)

to select the files (as in every directory there are also other *.xls file
whose columns haven't to be imported and I can't move those files form dir,
too)

 

[Ron de Bruin]

You can adapt my example
There are examples on this page
http://www.rondebruin.nl/copy3.htm

If you need more help post back

 

[uriel78]

I'm sorry and feel like an idiot...still can't obtain what I'm trying to
do...:-(((

I'm such a newbie with macro & excel...

Trying to explain what I need...:

In a new workboook I need to copy data in columns (I:J) from 4 different
sheets and

Every exixsting workbook has 4 sheets called "alfa", "beta", "gamma",
"delta". I need to copy (in a new sheet of a new workbook) columns I3:J203
of every sheet for every existing workbook

So in the new sheet, I've got
From Workbook1
I:J columns from "alfa" in A3:B203
I:J columns from "beta" in D3:E203
I:J columns from "gamma" in G3:H203
I:J columns from "delta" in J3:K203

From workboook2
I:J columns from "alfa" in M3:N203
I:J columns from "beta" in P3:Q203
I:J columns from "gamma" in S3:T203
I:J columns from "delta" in V3:W203

....and so on

And, maybe I wish I could put in cell A1,M1... the names of the workbooks
and in A2,B2,C2,D2....the names of the sheets....

really hope you can help me...


"