A macro that can deal with multiple files?

I know there must be a way to do this but I'm totally new at Excel macros and I don't have much time to figure this out to myself...

I work in a psych lab and we create an Excel file filled with about 50 worksheets for each participant we run through our studies. Usually, we write a macro that can take each participant's file and copy the cells needed in each worksheet to a file formatted in a certain way that we can copy and paste it right into another program. But then we have to find the name of the original file it takes the data from and replace it with the next file we want to deal with. So my first question is, is there a way to make a macro that can do this automatically with multiple files?

Second question: sometimes a cell that we want to copy into the master file is empty. When this happens the macro stops running. Is there a way to tell the macro to just skip over that cell and keep going if it's empty?

Thanks so much for any help you guys can offer me! Below is an example of the really simple type of macro we usually deal with (just imagine that that is one sheet and then it would move on to another sheet in the same file).

Sub Macro1()
Windows("Book1").Activate
Range("A16").Select
Selection.Copy
Windows("Book2").Activate
Range("A16").Select
ActiveSheet.Paste
Windows("Book1").Activate
Range("A19").Select
Selection.Copy
Windows("Book2").Activate
Range("B16").Select
ActiveSheet.Paste
Windows("Book1").Activate
Range("A27").Select
Selection.Copy
Windows("Book2").Activate
Range("C16").Select
ActiveSheet.Paste
End Sub

Hi,

From what I understand you do not need a macro ...

e.g. in sheet 2 go to cell A16 type =
then with your mouse go to sheet 1 cell A 16 and then press enter ...

that's it ...

HTH
Cheers
Carim

Originally Posted by Carim

From what I understand you do not need a macro ...

e.g. in sheet 2 go to cell A16 type =
then with your mouse go to sheet 1 cell A 16 and then press enter ...

that's it ...

Thanks for the response! Well, if I was dealing with a low volume of files that would definitely be feasible, but unfortunately we're talking about potentially hundreds of documents, and between 5-15 sheets in each document (from which I need to extract data from 3 cells which are always the same (e.g., F80, F130, and F165). So that would take almost as long as doing it the long way by hand, which many of the research assistants still do. Our macros now make it quicker, but I just know there's gotta be a way to program a macro to (a) recognize all the files of interest and (b) skip cells without data instead of running into an error. It's just I don't really know how to program! (But I can usually edit programming pretty decently.)

Hi,

Have a go with following macro, which will work even for empty cells ...

Sub Macro1()
Dim i As Integer
Dim Wk1 As Worksheet
Dim Wk2 As Worksheet

Let i = 16
Set Wk1 = Worksheets("Book1")
Set Wk2 = Worksheets("Book2")

Wk1.Activate
Range("A" & i).Copy
ActiveSheet.Paste Destination:=Wk2.Range("A" & i)
Wk1.Activate
Range("A" & i + 3).Copy
ActiveSheet.Paste Destination:=Wk2.Range("B" & i)
Wk1.Activate
Range("A" & i + 11).Copy
ActiveSheet.Paste Destination:=Wk2.Range("C" & i)
Application.CutCopyMode = xlCut
End Sub


HTH
Cheers
Carim

Man you are really helpful! So let me see if I can decipher what this macro is doing...

It's basically saying to cut the value of A16 from all worksheets in the first file and paste them into corresponding worksheets of a second file in the cell A16? And then to repeat that with A19 to B16, and A27 to C16? I feel like I'm totally off on this... Anyway, this is definitely beginning to make clear the type of macro that I really need to somehow build.

Hi,

Bear in mind, we have all started not knowing anything about VBA ...
Glad I could help ...
Thanks for the feedback


Carim