Easy question for the pro's!

[glendon]

I am using a macro in one workbook ("source") to open another workbook ("destination"). I have variables in "source" that I need to display in certain cells in "destination". I am very new to VBA and need a little direction. The values I need to show up in "destination" keep showing up in "source" instead. Thanks!

 

[VoG]

Can you post your code. Please use code tags

[code]
your code here
[/code]

 

[GTO]

Hi there,

When you open the other workbook, set good solid references to the other workbook, the sheet or sheets of interest within, etc. This way, you aren't at the mercy of whatever workbook/worksheet happens to be active.

Sub exa3()
Dim wb As Workbook, wks As Worksheet
    
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\Destination.xls")
    Set wks = wb.Worksheets("Whatever tab name")
    
    wks.Range("A1").Value = _
        ThisWorkbook.Worksheets("Source Sheet").Range("A1").Value
    
End Sub

Hope that helps,

Mark

 

[glendon]

Sub CommandButton1_Click()

Dim PackageName as String

PackageName = Sheets(1).Cells(13, 4).Value

Workbooks.Open Filename:="DestinationFile.xls"

Range("B9").Value = PackageName   

End Sub

All I want to do is have the variable PackageName show up in the Destination workbook. This just puts it back in the "source file". Thanks for the help. I'm a novice! \\

 

[kpark91]

Considering you have "Sheet1" in "DestinationFile" workbook.

Change Range("B9").Value = PackageName to

Workbooks("DestinationFile").Sheets("Sheet1").Range("B9").Value = PackageName

but i'm not too sure.. .cuz I'm a newbie..

 

[glendon]

That makes sense to me but for some reason I am getting the following error:

Run-time error '9':
Subscript out of range

 

[VoG]

Maybe

Workbooks("DestinationFile.xls").Sheets("Sheet1").Range("B9").Value = PackageName

Also change Sheet1 to the correct name of the sheet, if necessary.

 

[Sektor]

You should specify FULL path to workbook:

Workbooks.Open Filename:="C:\Test\Data\DestinationFile.xls"

 

[GTO]

Sub CommandButton1_Click()
Dim PackageName As String
Dim wb As Workbook
Dim wks As Worksheet
    
    '// IMO, best to ensure that we are grabbing the value from the correct wb, rather  //
    '// than whatever wb happens to be active.  We can do this by...
    PackageName = ThisWorkbook.Worksheets("Sheet1").Cells(13, 4).Value
    '..., Or, use the sheet's codename:
    PackageName = Sheet1.Cells(13, 4).Value
    
    '// Again, we can set references, so that we are explicit and ensure that we are    //
    '// aiming our data at the correct cell.  In your code, you were depending upon the //
    '// current directory being the correct one.  Better to explicitly call the correct //
    '// wb by using the fullmname.                                                      //
    Set wb = Workbooks.Open("C:\Folder\AnotherFolder\DestinationFile.xls")
    Set wks = wb.Worksheets("Whatever the destination sheet is named")
    
    wks.Range("B9").Value = PackageName
End Sub

 

[glendon]

GTO, this worked perfectly.

Thanks to all who commented to help me get this worked out - I'm learning!