Tony has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Access Intermediate course
Access Advanced course
Closing Workbooks
I have a number of similar spreadsheets that need to be combined and am trying to write a macro that opens workbooks using a variable name (StrFileName) in a for loop. I have no problem opening the workbook using
Workbooks.Open FileName:=StrFileName
The problem I am having is returning to the workbook to copy a second set of data and finally closing the workbook at the end.
I have tried
Windows(StrFileName).activate to move between workbooks
and
StrFileName. close to try and close the workbook but neither work.
What do I need to do?
RE: Closing Workbooks
Hi Tony
Thank you for your question
I am assuming that you are opening a workbook at a time, and copying the required data from each of those workbooks into a "consolidating workbook"
If this is the case then I suggest simply copying the cells directly using the following syntax
Workbooks("Consolidating Workbook").Sheets("Sheet Name").Cells(i,j) = workbooks(strFilename).sheets("Sheet Name").cells(i,j)This avoids the need to activate a workbook, and greatly speeds up your code
Regards
Stephen
RE: Closing Workbooks
Thanks Steven.
I had a go with your line of code but encountered some problems. The line I ended up with was
Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").RangeCells(RowNo + 1, 1), Cells(RowNo + 491, 10)= Workbooks(StrFileName).Sheets("MSFI").Range("a10:j500").
RowNo is a variable that counts the number of rows in the combined spreadsheet so that new data is entered into the next empty line.
This code did not work so I edited back for specific cells and worbook names and got a line that worked as follows:
Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").Range("d5") = Workbooks("Test1.xls").Sheets("MSFI").Range("d12").
Workbook Test1.xls was open
As soon as I change Workbooks("Test1.xls")... to
Workbooks(strFileName)... i get 'Run-time error'9' Subscript out of range. The variable strFileName is setting correctly so I am bact to my original problem.
Regards Tony
RE: Closing Workbooks
Hi Tony
Apologies for the delayed response. I have been away from the office for the last few weeks.
I have identified an error in the code you sent me.
Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").RangeCells(RowNo + 1, 1), Cells(RowNo + 491, 10)= Workbooks(StrFileName).Sheets("MSFI").Range("a10:j500").There should be a dot between the word range and cells, thus
Workbooks("COMBINED RETURN.XLS").Sheets("MSFI").Range.Cells(RowNo + 1, 1), Cells(RowNo + 491, 10)= Workbooks(StrFileName).Sheets("MSFI").Range("a10:j500").If this doesn't resolve the problem please let me know and I will investigate further
Regards
Stephen
