jetimmins
10-14-2011, 06:25 AM
Hello again!
I'm trying to make a macro which checks to see if a workbook is open, if it isn't then it opens it and pastes into a sheet. If it is, it checks to see if the first sheet has data in it, and if it does, it moves and pastes to a second sheet, etc.
Option Explicit
Sub ExportBid()
Dim ThatBook As Workbook, MyBook As Workbook
On Error Resume Next
Set MyBook = ActiveWorkbook
Set ThatBook = Workbooks("BidPricingModel.xlsm")
If ThatBook Is Nothing Then
Set ThatBook = Workbooks.Open _
("C:\Documents and Settings\jtimmins\My Documents\Quotes Project\BidPricingModel.xlsm")
MyBook.Activate
End If
If ThatBook.Sheets("a").Range("B17") = "" Then
Range("B14:C47").Copy
ThatBook.Sheets("a").Range("B17").PasteSpecial xlPasteValues
ElseIf ThatBook.Sheets("b").Range("B17") = "" Then
Range("B14:C47").Copy
ThatBook.Sheets("b").Range("B17").PasteSpecial xlPasteValues
Else
MsgBox "Both sheets are occupied"
End If
End Sub
So if the workbook is open, and if there is anything in the range on worksheet A, it should switch to worksheet B, see if anything is there, and if it's empty, paste there instead.
However, it seems to be trying to copy from the second worksheet with the first range and then pasting into the second range on the same worksheet, ignoring the first workbook entirely.
Again, very grateful for any input. It's probably something obvious I'm missing, but I've been staring at it for hours - perhaps my whole method is wrong ;(.
I'm trying to make a macro which checks to see if a workbook is open, if it isn't then it opens it and pastes into a sheet. If it is, it checks to see if the first sheet has data in it, and if it does, it moves and pastes to a second sheet, etc.
Option Explicit
Sub ExportBid()
Dim ThatBook As Workbook, MyBook As Workbook
On Error Resume Next
Set MyBook = ActiveWorkbook
Set ThatBook = Workbooks("BidPricingModel.xlsm")
If ThatBook Is Nothing Then
Set ThatBook = Workbooks.Open _
("C:\Documents and Settings\jtimmins\My Documents\Quotes Project\BidPricingModel.xlsm")
MyBook.Activate
End If
If ThatBook.Sheets("a").Range("B17") = "" Then
Range("B14:C47").Copy
ThatBook.Sheets("a").Range("B17").PasteSpecial xlPasteValues
ElseIf ThatBook.Sheets("b").Range("B17") = "" Then
Range("B14:C47").Copy
ThatBook.Sheets("b").Range("B17").PasteSpecial xlPasteValues
Else
MsgBox "Both sheets are occupied"
End If
End Sub
So if the workbook is open, and if there is anything in the range on worksheet A, it should switch to worksheet B, see if anything is there, and if it's empty, paste there instead.
However, it seems to be trying to copy from the second worksheet with the first range and then pasting into the second range on the same worksheet, ignoring the first workbook entirely.
Again, very grateful for any input. It's probably something obvious I'm missing, but I've been staring at it for hours - perhaps my whole method is wrong ;(.