allison
05-20-2008, 12:36 PM
I have 20 individual workbooks that once a week, need to have some data extracted and complied for some reports.
All of the files are named the same, except for a 4 digit year indicator - and the all have the same format. I need the data that is in columns A, B and AA - and will put that into columns A, B and C.
I have written this basic code to open the file & copy some of the information.
Sub CopyData()
Dim DestBook As Workbook
Dim SrcBook As Workbook
Dim LastRow As Long
Application.ScreenUpdating = False
Set DestBook = ThisWorkbook
Set SrcBook = Workbooks.Open("L:\Asbestos\Asbestos Logs by years\Asbestos Log1997.xls")
SrcBook.Activate
With ActiveSheet
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
DestBook.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
SrcBook.Activate
With ActiveSheet
End With
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
DestBook.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
SrcBook.Activate
With ActiveSheet
Range("AA2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
DestBook.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
SrcBook.Activate
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
I have a few problems with what I wrote and am looking for a bit of help, please.
1. When I am pasting the data into DestBook, how to I make sure that column A data goes into column A? column B data goes into column B? Right now, they all overlay each other in column A.
2. Because there are manu workbooks that this is going to be done on, I want the pasting to start in the first blank line. When I use the line
LastRow = Range("A" & .Rows.Count).End(xlUp).Row I get an error message. I've used that line in other code so I'm not sure if it's because there is only one row in DestBook (it's just a header row). Suggestions?
3. I really need to loop through this code for all 20 of the Workbooks. As I mentioned, the file names are all of the same except for the four digit year. Is there a way to set another variable to i (and looping from 1985 to 2008) and SrcBook to be Asbestos Log"i"?
I appreciate any suggestions!! Thanks in advance.
All of the files are named the same, except for a 4 digit year indicator - and the all have the same format. I need the data that is in columns A, B and AA - and will put that into columns A, B and C.
I have written this basic code to open the file & copy some of the information.
Sub CopyData()
Dim DestBook As Workbook
Dim SrcBook As Workbook
Dim LastRow As Long
Application.ScreenUpdating = False
Set DestBook = ThisWorkbook
Set SrcBook = Workbooks.Open("L:\Asbestos\Asbestos Logs by years\Asbestos Log1997.xls")
SrcBook.Activate
With ActiveSheet
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
DestBook.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
SrcBook.Activate
With ActiveSheet
End With
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
DestBook.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
SrcBook.Activate
With ActiveSheet
Range("AA2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
DestBook.Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
SrcBook.Activate
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
I have a few problems with what I wrote and am looking for a bit of help, please.
1. When I am pasting the data into DestBook, how to I make sure that column A data goes into column A? column B data goes into column B? Right now, they all overlay each other in column A.
2. Because there are manu workbooks that this is going to be done on, I want the pasting to start in the first blank line. When I use the line
LastRow = Range("A" & .Rows.Count).End(xlUp).Row I get an error message. I've used that line in other code so I'm not sure if it's because there is only one row in DestBook (it's just a header row). Suggestions?
3. I really need to loop through this code for all 20 of the Workbooks. As I mentioned, the file names are all of the same except for the four digit year. Is there a way to set another variable to i (and looping from 1985 to 2008) and SrcBook to be Asbestos Log"i"?
I appreciate any suggestions!! Thanks in advance.