interacting w/ 2 workbooks? 1

[peach255]

I have a workbook open, and then I prompt the user to pick another file to open on a new workbook. From there, I have some procedure to do some manipulation on the new workbook. Then I would like to go back to the 1st workbook and do some more manipulation of the 1st workbook , and then copy some cells from 2nd workbook and paste to the cells in the 1st workbook. I'm not sure how I can reference back and forth btwn the 2 workbooks. First, since the 2nd workbook will be the active one while it's running the procedure, how do I have it go back to the 1st workbook w/out prompting the user? Then how do I have it refer to the 2nd workbook cells and copy and paste to the 1st workbook w/out prompting for the file names as well?
Thank you!

 

[Stretchwickster]

In order to switch between workbooks you can use:

Workbooks("Name1").Activate
Workbooks("Name2").Activate

or

Workbooks(1).Activate
Workbooks(2).Activate

In order to copy a range use the following code:

Workbooks("Name1").Activate
Worksheets("Sheet1").Activate
Range("A1:D50").Copy Destination:=Workbooks("Name2").Worksheets("Sheet1").Range("B2")

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

Hi
Another way is to assign the workbooks to object variables. You should then be able to refer to each workbook through the variable name which cuts out the activating & selecting processes. As you say the user picks a file to open this is one way that could be done. This all assumes that the manipulation code is in the first workbook.

Sub OpenAndManipulate()
Dim SelectedFile
Dim wbTWO As Workbook
Dim wbME As Workbook

'get name for second workbook
SelectedFile = Application.GetOpenFilename _
    ("Excel files (*.xls), *.xls", , "Select a File")
If SelectedFile = False Then
    MsgBox "no file selected"
    Exit Sub
End If

Application.ScreenUpdating = False

'assign workbooks to variables
Set wbME = ThisWorkbook
Set wbTWO = Workbooks.Open(Filename:=SelectedFile)

'MANIPULATE WORKBOOKS EXAMPLE
With wbTWO
    .Worksheets(1).Range("A1").Copy _
        Destination:=wbME.Worksheets(1).Range("A1")
    .Worksheets(2).Rows("2:5").Interior.ColorIndex = 7
    .Close (False)
End With

wbME.Worksheets(2).Rows("2:5").Interior.ColorIndex = 33

Set wbME = Nothing
Set wbTWO = Nothing
Application.ScreenUpdating = True
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[peach255]

Thank you, Loomah! Assigning the workbook to Object variables worked great! Thanks so much! I appreciate it!

 

[Stretchwickster]

peach, I think Loomah deserves a star.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)