Hi me again....
My boss really needs to learn how to do this himself, but that is a different forum I think....
I need to take 12 individual workbooks and combine them into one, that in itself is not an issue, been doing that with another set of workbooks for a while now. What is different is that some of these workbooks maybe empty but a check on them needs to be performed to see if there is data before combining data.
On top of this, the "master" workbook is purged and rebuilt with every macro run so I am trying to include conditions that essentially say if the worksheet is empty (blank) then paste the data into cell A2 and append the rest below that. However if the master worksheet is not blank (ie some other person's data has been the first) then find the last occupied row and offset one down then paste to append.
What I have currently looks like this:
I have run this in break mode and it copies the data, but does not want to paste it in the master sheet, any help to get this running would be greatly appreciated!
Jeff
My boss really needs to learn how to do this himself, but that is a different forum I think....
I need to take 12 individual workbooks and combine them into one, that in itself is not an issue, been doing that with another set of workbooks for a while now. What is different is that some of these workbooks maybe empty but a check on them needs to be performed to see if there is data before combining data.
On top of this, the "master" workbook is purged and rebuilt with every macro run so I am trying to include conditions that essentially say if the worksheet is empty (blank) then paste the data into cell A2 and append the rest below that. However if the master worksheet is not blank (ie some other person's data has been the first) then find the last occupied row and offset one down then paste to append.
What I have currently looks like this:
Code:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim wbOpen As Workbook
strWbk = "C:\Documents and Settings\user\Desktop\Opportunistic Plays\Opportunistic Plays- Matt"
Set wbOpen = Workbooks.Open(strWbk)
wbOpen.Sheets(1).Select
ActiveSheet.Unprotect
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
If ActiveSheet.UsedRange.Address = "$A$3" And Range("A3") = "" Then
wbOpen.Close
End If
ActiveSheet.UsedRange.Offset(2, 0).Cells.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Windows("Opportunistic Plays- MASTER.xls").Activate
Worksheets("Opportunistic Play Master").Select
If Sheets(1).UsedRange.Address = "$A$2" And Range("A2") = "" Then
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.End(xlDown).Select
End If
wbOpen.CloseI have run this in break mode and it copies the data, but does not want to paste it in the master sheet, any help to get this running would be greatly appreciated!
Jeff
