Need to activate an open workbook

I have some code that copies the data on one spreadsheet and pastes it into another. The code knows the names of both workbooks and it currently uses the names in the code to activate the workbooks and copy and paste the data.

I want to have only two workbooks open and have the code copy the data from whatever other workbook is open regardless of the workbook name. Any ideas?

The workbook being copied from always has only one spreadsheet in it and the data range is always the same but the workbook name and spreadsheet name are always different.

Thanks

Assign a workbook variable (say, "wkb") to the workbook of interest when it's opened. Then you can reference that workbook.

ThisWorkbook refers to the workbook where the code is running.

Thanks for the help but your going to have to be a little more explicit for me to understand what you are telling me. I have only a very basic understanding of VB. But thanks again.

I am trying some code I got from another post but am having trouble with a reference in it, keeps erroring. Here is the code:

Option Explicit
Sub grabData()
Dim SourceWb As Workbook

Set SourceWb = ActiveWorkbook

SourceWb.UsedRange.Copy ThisWorkbook.Sheets.Cells(1, 1)
SourceWb.Close False
End Sub

Keeps erroring on the line: SourceWb.UsedRange.Copy ThisWorkbook.Sheets.Cells(1, 1)

I read a lot in VB help but as I said I'm a beginner so?

Thanks again for the help

Please read the Forum Rules and then wrap your code with Code Tags.

Sorry for the code identification issue. I'm new ti this forum, hope thid is correct.

I am trying some code I got from another post but am having trouble with a reference in it, keeps erroring. Here is the code:

Option Explicit
Sub grabData()
Dim SourceWb As Workbook

Set SourceWb = ActiveWorkbook

SourceWb.UsedRange.Copy ThisWorkbook.Sheets.Cells(1, 1)
SourceWb.Close False
End Sub

Keeps erroring on the line: SourceWb.UsedRange.Copy ThisWorkbook.Sheets.Cells(1, 1)

I read a lot in VB help but as I said I'm a beginner so?

Thanks again for the help

OK, so here is what I’ve come up with on my own. One sub named "WorkbookCollection" uses a for/each next statement to work through any and all open workbooks and another sub named "SliceAndDice" manipulates the spreadsheet data. It all seems to work fine.

I need tow things, suggestions on anything I might be missing to clean this up since I'm an armature, and a suggestion on how I can keep the copy and paste procedure from copying a text box I have on my SliceAndDice spreadsheet.

Thanks again for the help.

Sub WorkbookCollection()
'
' WorkbookCollection Macro
' Macro first recorded 8/4/2008 by Rod Cathcart
'

'
    For Each w In Workbooks
        If w.Name <> ThisWorkbook.Name Then
            
            SliceAndDice w.Name
        
            w.Close savechanges:=True
            
        End If
    Next w

End Sub

Sub SliceAndDice(NextWorkbook)
'
' SliceAndDice Macro
' Macro first recorded 8/4/2008 by Rod Cathcart
'

' This Macro is a work in progress, I plan to pass a workbook
' name to this sub and run the slice and dice on that workbook.

    'Copy columns D2-AE from source workbook and paste into DataDicer
    Windows(NextWorkbook).Activate
    Range("D2:AE740").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DataDicer.xls").Activate
    Range("B2").Select
    ActiveSheet.Paste
    '
    'Copy columns AF-AG from source workbook and paste into DataDicer
    Windows(NextWorkbook).Activate
    Range("AF2:AG740").Select
    Selection.Copy
    Windows("DataDicer.xls").Activate
    Range("AF2").Select
    ActiveSheet.Paste
    '
    'Copy columns A from source workbook and paste to column AH in DataDicer
    Windows(NextWorkbook).Activate
    Range("A2:A740").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DataDicer.xls").Activate
    Range("AH2").Select
    ActiveSheet.Paste
    '
    'Copy columns C and paste to column A
    Windows(NextWorkbook).Activate
    Range("C2:C740").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DataDicer.xls").Activate
    Range("A2").Select
    ActiveSheet.Paste
    '
    'Delete all data from source workbook and replace it with
    'reorganized data from the DataDicer
    Windows(NextWorkbook).Activate
    Range("A1:AG740").Select
    Selection.ClearContents
    Windows("DataDicer.xls").Activate
    Range("A1:AH740").Select
    Selection.Copy
    Windows(NextWorkbook).Activate
    Range("A1").Select
    ActiveSheet.Paste
    '
    'Delete all data below row 1 of DataDicer, format columns on source workbook
    Windows("DataDicer.xls").Activate
    Range("A2:AH740").Select
    Selection.ClearContents
    Range("A1").Select
    Windows(NextWorkbook).Activate
    Columns("A:AH").Select
    Columns("A:AH").EntireColumn.AutoFit
    Range("A1").Select
    '

End Sub