switching between worksheets

[demodren]

Hi all, I have below code which I use to copy/paste data within same workbook but different tabs.

'copy all names 
With Sheets("data")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(3, 1), .Cells(LastRow, 1)).Copy Sheets("summary_info").Cells(3, 1)
    End With

I'm trying to modify this this because now the data is located in a different spreadsheet which I tell excel to open

1) I tell Excel to open my data spreadsheet.. (now is active spreadsheet)

2) Copy column 1 in data.xls

3) switch to spreadsheet summary.xls (tab summary_info) and paste into column 1

4) Now I need excel to go back to data spreadsheet and copy a different column and repeat the process..I need to copy 8 columns in total.. perhaps I can just just choose all columns at once so I don't have to go back and forth? the columns are not in order though..


Any help is very appreciated! thanks so much

Dan

 

[demodren]

not sure if perhaps my post is little unclear.. basically I am trying to avoid repeating the below code 8 times(for different column) where I need to copy and paste different random columns into my summary sheet. I was told it is pointless to keep activating the sheets over and over..

hope this explains little better.. thanks so much!

    Windows(data_main_file.xls).Activate
    Sheets(data).Select
    Range("BP3:BP" & LastRow).Select
    Selection.Copy
    Windows("summary_info.xls").Activate
    Sheets("data").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

the code that I found that works within 1 workbook is below.. but I can't seem to modify to work for above...

'copy all names 
With Sheets("data")
        LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(3, 1), .Cells(LastRow, 1)).Copy Sheets("summary_info").Cells(3, 1)
    End With