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 |
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 |