How to activate already open workbook through VBA code

[Daiwik]

Hi All,

I'm very new in writing VBA. Please help me in activating already open workbook. Following is the code i'm using but don't know how to complete it. Please help.

Sub AssessmentDataPaste()
Dim MyPath As String

MyPath = Sheets("Main").Range("C7").Value

' Pop-up a msgbox if NO path is available in the field.
If MyPath = "" Then
MsgBox ("Please provide a valid destination/file path.")
End If

Workbooks.Open Filename:=MyPath

Range("F12:F3000").Select
Selection.Copy
Workbooks("Ingestion_Template.xlsm").Activate
Sheets("IngestionTemplate").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select


Workbooks.Activate
Range("P12:P3000").Select
Selection.Copy
Workbooks("Ingestion_Template.xlsm").Activate
Sheets("IngestionTemplate").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C7").Select


End Sub

 

[sastoka]

Hi There

Replace the line by this

Workbooks("workbookname").Activate

that should do the trick


regards

 

[CsJHUN]

Hi daivik!

I suggest this:

Sub AssessmentDataPaste()
Dim MyPath As String

MyPath = Sheets("Main").Range("C7").Value

' Pop-up a msgbox if NO path is available in the field.
If MyPath = "" Then
MsgBox ("Please provide a valid destination/file path.")
End If

Workbooks.Open Filename:=MyPath
[COLOR=#ff0000]owbn = Activeworkbook.Name  'this will add the opened workbook name to a variant[/COLOR]

Range("F12:F3000").Select
Selection.Copy
Workbooks("Ingestion_Template.xlsm").Activate
Sheets("IngestionTemplate").Activate
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B7").Select


Workbook[COLOR=#ff0000]s(owbn).A[/COLOR]ctivate
Range("P12:P3000").Select
Selection.Copy
Workbooks("Ingestion_Template.xlsm").Activate
Sheets("IngestionTemplate").Activate
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C7").Select


End Sub

 

[My Aswer Is This]

Here is a example of how you can do things without needing to activate and select.

Sub Test_Me()
Workbooks("Book1").Sheets(1).Range("A1").Copy Destination:=Workbooks("Book2").Sheets(1).Range("A1")
End Sub

 

[My Aswer Is This]

Or if you want to use PasteSpecial try this:

Sub Test_Me()
Workbooks("Book1").Sheets(1).Range("A1").Copy
Workbooks("Book2").Sheets(1).Range("A2").PasteSpecial xlPasteValues
End Sub

 

[Daiwik]

Hi CsJHUN,

Many thanks
It's running with all perfections.