Solved: Macro: copying across several worksheets

[jetimmins]

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.

[VBA]
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
[/VBA]

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 ;(.

[mancubus]

hello jetimmins.

i cant repeat your problem.

your code opens thatbook, copies the mybook's activesheet's range to thatbook's sheet a. if the cell in sheet a is not blank then copies the range to sheet b. and if the cell in sheet b is not blank, displays the message.

[jetimmins]

For me, the first time it's executed it copies from worksheet (a) back onto worksheet (a), then works the second time for checking and copy/pasting on worksheet (b). Grrrrrr.

[jetimmins]

Hm, I seemed to have fixed it by changing

[VBA]If ThatBook.Sheets("a").Range("B17") = "" Then
Range("B14:C47").Copy
ThatBook.Sheets("a").Range("B17").PasteSpecial xlPasteValues [/VBA]

to [VBA]If ThatBook.Sheets("a").Range("B17") = "" Then
Workbooks("MASTERSHEETMACRO.xlsm").Activate
Range("B14:C47").Select
Selection.Copy
ThatBook.Sheets("a").Range("B17").PasteSpecial xlPasteValues[/VBA]

It wasn't even working using Workbooks(Mybook).Activate

Not sure why it's fixed, but it is!