Macro in one workbook that names a range in a different workbook

[Tony Bender]

I have an application consisting of two workbooks located in the same
directory (C:\Data\Wkbk1.xls and C:\Data\Wkbk2). There is a userform
in wkbk1 where the user makes a selection from a listbox. The result
of this selection identifies both a worksheet name (sht2) and a range
(rng1). The worksheet name appeare in range("o141") on Sht1 in
Wrkb1. The range appear in range("r141") also on sht1 in Wrkb1.

I am trying to write a macro that opens the second workbook (wrkb2),
if it is not already open, and then from 'sht2' copies the range
'rng1' and then pastes that range onto range("A2") on 'sht3' back in
Wrkbk1.

But this is not working. Can anyone help me identify what I'm doing
wrong and how to write the correct code. Here's what I've got so far:

Sub CopyTarget()
Dim Wrkbk1 as Workbook
Dim Wrkbk2 as Workbook
Dim Sht2 as Range
Dim Rng1 As Range

Wrkbk2 = C:\Data\Wrkbk2.xls
Set Sht2 = Sheets(“Sht1”).Range(“o141”)
Set Rng1 = Sheets("Sht1").Range("r141")

ThisWorkbook=C:\Data\Wrkbk1.xls

Workbook.wrkbk2.open

Range(Rng1).Select
Selection.Copy

Wrkbk1.Sht3.Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

End Sub


Thank you

 

[Dave Peterson]

Option Explicit
Sub CopyTarget2()
dim ShtName as string
dim RngAddr as string
dim wkbk2 as workbook
dim wks2 as worksheet

with thisworkbook.worksheets("Sht1")
shtname = cstr(.range("o141").value)
rngaddr = .range("r141").value
end with

set wkbk2 = nothing
on error resume next
set wkbk2 = workbooks("wkbk2.xls") '<--just the filename--no path!
on error goto 0

if wkbk2 is nothing then
'it's not open
on error resume next
'this includes the drive, path, and filename!
set wkbk2 = workbooks.open(filename:="C:\data\wkbk2.xls")
on error goto 0

if wkbk2 is nothing then
msgbox "wkbk2 not open and can't be found!"
exit sub
end if
end if

set wks2 = nothing
on error resume next
set wks2 = wkbk2.worksheets(shtname)
on error goto 0

if wks2 is nothing then
msgbox "Wkbk2 doesn't contain a worksheet with the correct name"
exit sub
end if

wks2.range(rngaddr).copy _
destination:=thisworkbook.worksheets("Sht3").range("a2")

End sub

Untested, uncompiled. Watch for typos!

ThisWorkbook is not a variable that you can change. It's a reference to the
workbook that owns the code.

And it assumes that the workbook with the code has sheets named Sht1 and Sht3.

 

[Tony Bender]

Dave,

Many thanks...this works great...I appreciate your time

Tony Bender