BethPritchard
New Member
- Joined
- Oct 25, 2011
- Messages
- 2
Re: Excel 2007
As a basic VBA user, I have a macro that I would like to create to copy values from one range to another range in a separate workbook. I'm sure this should be straight forward but after a number of hours of failure I've been driven to despair.
I have 2 open spreadsheets:
Workbook(“DEF”)
Workbook(“ABC”)
I want to copy the following range:
Workbook(“ABC”).Sheets(“123”).Range(“ D1:G4”)
to
Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
This is easy if I do the following:
Dim ABCRange As Range
Dim DEFRange As Range
Set ABCRange = Workbook(“ABC”).Sheets(“123”).Range(“D1:G4”)
Set DEFRange = Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
DEFRange.Value = ABCRange.Value
However, ABCRange and DEFRange are relative (I’m not sure if that is the correct term) i.e. the range address varies.
In order to know what the correct range is, I have created a formula in the spreadsheet itself.
So, in Range(“A1”) I have a formula that delivers a value and that value is:
Workbook(“ABC”).Sheets(“123”).Range(“D1:G4”)
The formula delivers a different value depending on updates to the spreadsheet, so, the value could change to Workbook(“ABC”).Sheets(“123”).Range(“D10:G40”) etc.
In Range(“A2”) I have a formula that delivers a value of:
Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
With this in mind, I would like to set ABCRange and DEFRange to the value of A1 and A2 (a kind of indirect but in VBA)
To my mind it would look something like this:
Dim ABCRange As Range
Dim DEFRange As Range
Set ABCRange = Range(“A1”).Value
Set DEFRange = Range(“A2”).Value
DEFRange.Value = ABCRange.Value
But I get an error when I get to the ‘Set ABCRange = Range(“A1”).Value’
As an alternative I have tried:
Dim ABCString As String
Dim DEFString As String
Dim ABCRange As Range
Dim DEFRange As Range
ABCString = Range(“A1”).Value
DEFString = Range(“A2”).Value
Set ABCRange = ABCString
Set DEFRange = DEFString
DEFRange.Value = ABCRange.Value
This time I get an error when I get to Set ABCRange = ABCString
This is driving me to mad. Any help much appreciated.
As a basic VBA user, I have a macro that I would like to create to copy values from one range to another range in a separate workbook. I'm sure this should be straight forward but after a number of hours of failure I've been driven to despair.
I have 2 open spreadsheets:
Workbook(“DEF”)
Workbook(“ABC”)
I want to copy the following range:
Workbook(“ABC”).Sheets(“123”).Range(“ D1:G4”)
to
Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
This is easy if I do the following:
Dim ABCRange As Range
Dim DEFRange As Range
Set ABCRange = Workbook(“ABC”).Sheets(“123”).Range(“D1:G4”)
Set DEFRange = Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
DEFRange.Value = ABCRange.Value
However, ABCRange and DEFRange are relative (I’m not sure if that is the correct term) i.e. the range address varies.
In order to know what the correct range is, I have created a formula in the spreadsheet itself.
So, in Range(“A1”) I have a formula that delivers a value and that value is:
Workbook(“ABC”).Sheets(“123”).Range(“D1:G4”)
The formula delivers a different value depending on updates to the spreadsheet, so, the value could change to Workbook(“ABC”).Sheets(“123”).Range(“D10:G40”) etc.
In Range(“A2”) I have a formula that delivers a value of:
Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
With this in mind, I would like to set ABCRange and DEFRange to the value of A1 and A2 (a kind of indirect but in VBA)
To my mind it would look something like this:
Dim ABCRange As Range
Dim DEFRange As Range
Set ABCRange = Range(“A1”).Value
Set DEFRange = Range(“A2”).Value
DEFRange.Value = ABCRange.Value
But I get an error when I get to the ‘Set ABCRange = Range(“A1”).Value’
As an alternative I have tried:
Dim ABCString As String
Dim DEFString As String
Dim ABCRange As Range
Dim DEFRange As Range
ABCString = Range(“A1”).Value
DEFString = Range(“A2”).Value
Set ABCRange = ABCString
Set DEFRange = DEFString
DEFRange.Value = ABCRange.Value
This time I get an error when I get to Set ABCRange = ABCString
This is driving me to mad. Any help much appreciated.