Paste data with a macro?
 

Hi,

Can anyone tell me why the following code causes a Dr. Watson error each
time I try and run it please? I am trying to open a file from the temp
folder, copy data from it and paste this data into another workbook (name
unkown as it will be user defined):

Sub Data_Extract ()

Workbooks.Open FileName:="C:\Temp\DataFile.xls"
Sheets ("Sheet 1").Range("C2:D6000 , H2:H6000").Copy
MsgBox "Data Copied"
If Workbooks.Count 2 Then
MsgBox "Close unneccessary files and restart"
Else
For Each Bk In Workbooks
If Bk.Windows(1).Visible = True Then
If Bk.Name < "DataFile.xls" Then
Bk.Activate
MsgBox "Active"
With ThisWorkbook.Sheets ("Data Sheet")
Range ("A2").Select
'Selection.Paste <= This is where the
'ActiveSheet.Paste macro trips up
End With
End If
End If
Next
End If
End Sub

I am very new to Visual basic and have botched this code together from
various other postings.

This macro will run from a user-defined workbook, so all references to it
must be 'not the other file' (if you see what I mean).

I initially had problems referring between workbooks, but can now do so (I
use the MsgBox to follow the code as it executes). The problem I now face is
pasting the copied data from one workbook into the other.

I'd be very grateful for any advice offered.

Thanks.

Ewan

Paste data with a macro?
 

I've worked it out:

Range ("A2").Select
ActiveSheet.Paste
becomes
Range ("A2:C6000").Activate
ActiveSheet.Paste

Sorry if I've wasted anyone's time. Thanks

"ewan7279" wrote:

Hi,

Can anyone tell me why the following code causes a Dr. Watson error each
time I try and run it please? I am trying to open a file from the temp
folder, copy data from it and paste this data into another workbook (name
unkown as it will be user defined):

Sub Data_Extract ()

Workbooks.Open FileName:="C:\Temp\DataFile.xls"
Sheets ("Sheet 1").Range("C2:D6000 , H2:H6000").Copy
MsgBox "Data Copied"
If Workbooks.Count 2 Then
MsgBox "Close unneccessary files and restart"
Else
For Each Bk In Workbooks
If Bk.Windows(1).Visible = True Then
If Bk.Name < "DataFile.xls" Then
Bk.Activate
MsgBox "Active"
With ThisWorkbook.Sheets ("Data Sheet")
Range ("A2").Select
'Selection.Paste <= This is where the
'ActiveSheet.Paste macro trips up
End With
End If
End If
Next
End If
End Sub

I am very new to Visual basic and have botched this code together from
various other postings.

This macro will run from a user-defined workbook, so all references to it
must be 'not the other file' (if you see what I mean).

I initially had problems referring between workbooks, but can now do so (I
use the MsgBox to follow the code as it executes). The problem I now face is
pasting the copied data from one workbook into the other.

I'd be very grateful for any advice offered.

Thanks.

Ewan