Macro to set a path

[Chandresh]

Hi all ,

i have a macro which copies data from one workbook to current work book which is working fine.

i need a help if i want to copy the data to some other file with the help of giving path insted of active workbook
Path - "C:\Result\ABC.xlsx"

at present i am using below code-

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = Sheet2



full code

Sub Foocopy()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
'>>>>> need to copy the data to ABC.xlsx
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = Sheet2
'-------------------------------------------------------------
'Open file with data to be copied

vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If

'--------------------------------------------------------------
'Copy Range
wsCopyFrom.Range("B6").Copy
wsCopyTo.Range("B5").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


wsCopyFrom.Range("B6").Copy
wsCopyTo.Range("B6").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False



'Close file that was opened
wbCopyFrom.Close SaveChanges:=False
End Sub

 

[bobsan42]

try like this:

[COLOR=#333333]Set wbCopyTo = [/COLOR]Application.Workbooks.Open ([COLOR=#333333]"C:\Result\ABC.xlsx"[/COLOR]) 
[COLOR=#333333]Set wsCopyTo = wbCopyTo.Sheet2[/COLOR]

 

[Chandresh]

Thanks for your help