Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in one workbook that names a range in a different workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in one workbook that names a range in a different workbook
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in one workbook that names a range in a different workbook
Dave,
Many thanks...this works great...I appreciate your time Tony Bender |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make range names universal in workbook | Excel Discussion (Misc queries) | |||
How do I call up a line of code that references a cell/range in theactive workbook workbook where I am running my macro from? | Excel Programming | |||
How Can I Loop through all the range names in a workbook in VBA? | Excel Programming | |||
Copying Range Names to another open workbook | Excel Programming | |||
Need a macro to copy a range in one workbook and paste into another workbook | Excel Programming |