Copy, Paste and Close File Macro

[LightningBolt]

Hey,

First time posting here and I'm hoping somebody can be of some help. What I'm looking to do is above my excel skills at the moment.

I've got 200 odd workbooks that are excel versions of system reports. What I want to do is to take data from each workbook and paste it into a brand new "master" workbook so that I can run pivots off it. I tried to record a macro to do this as follows:

1) First I copy the data I want from each workbook that I want copied in (lets call it Workbook 1)
2) I then select "Record Macro" and pasted it into Workbook Master.
3) I then close Workbook 1 and finish recording.
4) I open up Workbook 2 and select the data again I want copied and try to run the macro but an error message shows up.

Ideally the macro could do the following in workbook 1 (i.e. the open workbook)

1) Select and copy all data in the rows from Cell A9 (Cell A8 always has the following text "Opening Balance" to the row just before a cell in Column A says "Closing Balance") - Some reports have maybe 10 rows of data, others could have 50 rows of data.
2) The data selected will be pasted into the Master Workbook in cell A2.
3) Workbook 1 will then close.
4) When I then open Workbook 2 I would like addiitonal data to be pasted in the next empty row.
C
an anybody please advise?

Many thanks!

 

[tlowry]

Here is my interpretation of the solution to your request

The files to be copied are listed in the “Master” workbook in a sheet called “Files” in column A. If the files are not in the default directory, they must include the full path in the file name i.e. C:\temp\abc.xls

To start the process:

• Open the “Master”
• Copy these two subs to a module in “Master”
• Create a sheet named “Files”
• Put in the names of the files to be copied in “Files” column A
• Run GetFiles

Sub copyrows(swbname)

If LCase(Right(swbname, 4)) <> ".xls" Then swbname = swbname & ".xls"
On Local Error Resume Next
Set x = Workbooks.Open(Filename:=swbname)
If IsEmpty(x) Then

MsgBox Err.Description
Err.Clear
Exit Sub
​

End If
Windows(swbname).Activate
istart = 10
iend = Range("A" & Rows.Count).End(xlUp).Row - 1
Rows(istart & ":" & iend).Copy
Range("A9").Select
Windows("Master 1.xls").Activate
istart = WorksheetFunction.CountA(Range("A:A")) + 2
If istart = 1 Then istart = 2
ActiveSheet.Range("A" & istart).Select
ActiveSheet.Paste
ActiveSheet.Range("A2").Select
Application.DisplayAlerts = False
Windows(swbname).Close False
Application.DisplayAlerts = True
​

End Sub

Sub GetFiles()

Application.ScreenUpdating = False
i = 0
Do While Sheets("Files").Range("A1").Offset(i, 0).Text <> ""

copyrows Sheets("Files").Range("A1").Offset(i, 0).Text
i = i + 1
​

Loop
Application.ScreenUpdating = True
​

End Sub