Copying data from another Workbook VBA and pasting into current wb

[Christof]

Hi can anyone help me with this..

I need to select all the data from the ALL_QUESTIONS_NONCRUISE.xls workbook (the tab is also called ALL_QUESTIONS_NONCRUISE), and then paste it into my current workbook (dont want to type the name of the file the macro is in as it changes every week with the date.. hense why I am trying the ThisWorkBook function.

Anyway, something I'm doing isn't working. When I run the macro I get the red X and 400 message.

Please help

Sub uzzzzz()
'
' update Macro
'

'
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Workbooks.Open Filename:= _
        "S:\DatabaseMarketing\CSQs\Reporting\Data files\ALL_QUESTIONS_NONCRUISE.xls"
    Cells.Select
    Selection.Copy

    ThisWorkbook.Activate
    Sheets("CSQData").Select
    Range("A1").Select
    ActiveSheet.Paste
       
    Windows("ALL_QUESTIONS_NONCRUISE.xls").Activate
    ActiveWindow.Close
   

   
    Sheets("CSQ Scores").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
   

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
       
   
End Sub

 

[Hui]

a few comments

The lines

Workbooks.Open Filename:= _
"S:\DatabaseMarketing\CSQs\Reporting\Data files\ALL_QUESTIONS_NONCRUISE.xls"
Cells.Select

will assume the workbook opens on the correct worksheet

If it doesn't it will copy the worksheet that was last used
Unless there is only 1 worksheet you should add a line to set the worksheet

Do you have the correct permissions to access all the workbooks?

Can you post them here?

 

[chirayu]

Sub uzzzzz()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Define variables
Dim CurrWB As String
Dim OpenWB As String

'Set Variables
CurrWB = ActiveWorkbook.Name
OpenWB = "S:\DatabaseMarketing\CSQs\Reporting\Data files\ALL_QUESTIONS_NONCRUISE.xls"

'Open Noncruise and Copy Data
Workbooks.Open Filename:=OpenWB
Worksheets("ALL_QUESTIONS_NONCRUISE").Select
Cells.Copy

'Paste data
Windows(CurrWB).Activate
Sheets("CSQData").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Close Noncruise
Workbooks("ALL_QUESTIONS_NONCRUISE.xls").Close savechanges:=False

'refresh pivot
Sheets("CSQ Scores").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh


Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub