Help with macro
 

I need to copy a range from one worksheet and paste the values into another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

Thanks Rick. The reason for using indirect is that the cell where the range
paste will start depends on the results of other calculations. To simplify
it, I have a range of monthly loan repayments. These are to be pasted into
another worksheet, but the first cell of the target range is determined by a
number of months by which the first loan repayment is deferred. Could be 3,
4, 6 etc. - its a variable, and that's why I'm trying to use the indirect
function so that I can offset the target range by the number of deferred
months. I know how it works in excel, I don't know how (if) it works in VBA.

"Rick Rothstein" wrote:

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

Don't "simplify"... it doesn't really help. First off, you don't need to use
the Indirect function, mainly because VB doesn't have one. In order for us
to be able to help you, you need to tell us where your source data is, how
you determine what part of it you want and how you determine where to put it
(all of that in detail). We will be more than happy to show you how do
accomplish what you want, but you need to give us the details so that we can
figure out what you are simply taking for granted that you know.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
Thanks Rick. The reason for using indirect is that the cell where the
range
paste will start depends on the results of other calculations. To simplify
it, I have a range of monthly loan repayments. These are to be pasted into
another worksheet, but the first cell of the target range is determined by
a
number of months by which the first loan repayment is deferred. Could be
3,
4, 6 etc. - its a variable, and that's why I'm trying to use the indirect
function so that I can offset the target range by the number of deferred
months. I know how it works in excel, I don't know how (if) it works in
VBA.

"Rick Rothstein" wrote:

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you
actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy
Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at
cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into
another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

Thanks once more. There's not much I can do other than simplify the problem.
Its a complex workbook of 14 worksheets and I don't think I could explain it
well enough without sending or posting the whole workbook (which I can't do).
Both the source data range, and the target range are determined by values in
other worksheets, and I've used the indirect function to successfully select
the source data, but couldn't figure out a formula to select the target
range, and I was hoping there'd be a similar function to "indirect" in VB,
but since there's not, its back to the drawing board ....

Thanks anyway.

"Rick Rothstein" wrote:

Don't "simplify"... it doesn't really help. First off, you don't need to use
the Indirect function, mainly because VB doesn't have one. In order for us
to be able to help you, you need to tell us where your source data is, how
you determine what part of it you want and how you determine where to put it
(all of that in detail). We will be more than happy to show you how do
accomplish what you want, but you need to give us the details so that we can
figure out what you are simply taking for granted that you know.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
Thanks Rick. The reason for using indirect is that the cell where the
range
paste will start depends on the results of other calculations. To simplify
it, I have a range of monthly loan repayments. These are to be pasted into
another worksheet, but the first cell of the target range is determined by
a
number of months by which the first loan repayment is deferred. Could be
3,
4, 6 etc. - its a variable, and that's why I'm trying to use the indirect
function so that I can offset the target range by the number of deferred
months. I know how it works in excel, I don't know how (if) it works in
VBA.

"Rick Rothstein" wrote:

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you
actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy
Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at
cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into
another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

Let's see if I can give you a general answer. Here I will assume everything is in variables (you will have to translate them into what you actually have); I'll use the word "source" and "copy" in the variable names to keep the two locations separate.

With Worksheets(SourceSheetName)
.Range(.Cells(SourceStartRow, SourceStartColumn), ..Cells(CopyTopLeftCellRow, CopyTopLeftCellColumn)).Copy _
Worksheets(CopySheetName).Cells(CopyTopLeftCellRow , CopyTopLeftCellColumn)
End With

Pay attention to the leading "dots" in front of the Range and Cells references in the first part of the statement... they make them reference back to the worksheet that is the object of the With statement. Note that I used line continuation characters (the space/underbar at the end of each line) to prevent your newsreader from breaking up the statement between the With and End With in awkward ways, but, if you remove the line continuation characters, that is a single-line statement. Here is the same code with the variable names reduced to just their uppercase letters in order to shorten the code enough not to need the line continuations (if it still looks wrapped, widen your newsreader window and it should "unwrap")...

With Worksheets(SSN)
.Range(.Cells(SSR, SSC), .Cells(CTLCR, CTLCC)).Copy Worksheets(CSN).Cells(CTLCR, CTLCC)
End With

When you Dim your variables... the sheet names should be String, the rows should be Long and the columns should be Variant (they can be designated as either a number or letter(s) depending on how you designated the columns; that is, for Column E, as an example, you can use either 5 or "E" in the variable).

--
Rick (MVP - Excel)


"Ken G." wrote in message ...
Thanks once more. There's not much I can do other than simplify the problem.
Its a complex workbook of 14 worksheets and I don't think I could explain it
well enough without sending or posting the whole workbook (which I can't do).
Both the source data range, and the target range are determined by values in
other worksheets, and I've used the indirect function to successfully select
the source data, but couldn't figure out a formula to select the target
range, and I was hoping there'd be a similar function to "indirect" in VB,
but since there's not, its back to the drawing board ....

Thanks anyway.

"Rick Rothstein" wrote:

Don't "simplify"... it doesn't really help. First off, you don't need to use
the Indirect function, mainly because VB doesn't have one. In order for us
to be able to help you, you need to tell us where your source data is, how
you determine what part of it you want and how you determine where to put it
(all of that in detail). We will be more than happy to show you how do
accomplish what you want, but you need to give us the details so that we can
figure out what you are simply taking for granted that you know.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
Thanks Rick. The reason for using indirect is that the cell where the
range
paste will start depends on the results of other calculations. To simplify
it, I have a range of monthly loan repayments. These are to be pasted into
another worksheet, but the first cell of the target range is determined by
a
number of months by which the first loan repayment is deferred. Could be
3,
4, 6 etc. - its a variable, and that's why I'm trying to use the indirect
function so that I can offset the target range by the number of deferred
months. I know how it works in excel, I don't know how (if) it works in
VBA.

"Rick Rothstein" wrote:

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you
actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy
Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at
cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into
another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

I must be missing something here. Doesn't your code below assume that we
already know the location of the source range? I don't know, and that's why I
used the "Indirect" function in Excel to work out where the source range
starts and ends. If time permits I'll see if I can re-create my problem in a
sheet that I can post or send to you. In the meantime I've overcome it by
hard-coding the data into place for the most likely scenario. This removes
the flexibilty of selecting the pre-payment period but it'll do for now.

I appreciate your time and effort in trying to assist me here. Thanks.

"Rick Rothstein" wrote:

Let's see if I can give you a general answer. Here I will assume everything is in variables (you will have to translate them into what you actually have); I'll use the word "source" and "copy" in the variable names to keep the two locations separate.

With Worksheets(SourceSheetName)
.Range(.Cells(SourceStartRow, SourceStartColumn), ..Cells(CopyTopLeftCellRow, CopyTopLeftCellColumn)).Copy _
Worksheets(CopySheetName).Cells(CopyTopLeftCellRow , CopyTopLeftCellColumn)
End With

Pay attention to the leading "dots" in front of the Range and Cells references in the first part of the statement... they make them reference back to the worksheet that is the object of the With statement. Note that I used line continuation characters (the space/underbar at the end of each line) to prevent your newsreader from breaking up the statement between the With and End With in awkward ways, but, if you remove the line continuation characters, that is a single-line statement. Here is the same code with the variable names reduced to just their uppercase letters in order to shorten the code enough not to need the line continuations (if it still looks wrapped, widen your newsreader window and it should "unwrap")...

With Worksheets(SSN)
.Range(.Cells(SSR, SSC), .Cells(CTLCR, CTLCC)).Copy Worksheets(CSN).Cells(CTLCR, CTLCC)
End With

When you Dim your variables... the sheet names should be String, the rows should be Long and the columns should be Variant (they can be designated as either a number or letter(s) depending on how you designated the columns; that is, for Column E, as an example, you can use either 5 or "E" in the variable).

--
Rick (MVP - Excel)


"Ken G." wrote in message ...
Thanks once more. There's not much I can do other than simplify the problem.
Its a complex workbook of 14 worksheets and I don't think I could explain it
well enough without sending or posting the whole workbook (which I can't do).
Both the source data range, and the target range are determined by values in
other worksheets, and I've used the indirect function to successfully select
the source data, but couldn't figure out a formula to select the target
range, and I was hoping there'd be a similar function to "indirect" in VB,
but since there's not, its back to the drawing board ....

Thanks anyway.

"Rick Rothstein" wrote:

Don't "simplify"... it doesn't really help. First off, you don't need to use
the Indirect function, mainly because VB doesn't have one. In order for us
to be able to help you, you need to tell us where your source data is, how
you determine what part of it you want and how you determine where to put it
(all of that in detail). We will be more than happy to show you how do
accomplish what you want, but you need to give us the details so that we can
figure out what you are simply taking for granted that you know.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
Thanks Rick. The reason for using indirect is that the cell where the
range
paste will start depends on the results of other calculations. To simplify
it, I have a range of monthly loan repayments. These are to be pasted into
another worksheet, but the first cell of the target range is determined by
a
number of months by which the first loan repayment is deferred. Could be
3,
4, 6 etc. - its a variable, and that's why I'm trying to use the indirect
function so that I can offset the target range by the number of deferred
months. I know how it works in excel, I don't know how (if) it works in
VBA.

"Rick Rothstein" wrote:

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you
actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy
Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at
cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into
another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)

Help with macro
 

Yes, if you could post for all to see, or send me a sample sheet (remove the NO.SPAM stuff), that would be good. While I think a solution exists for your situation, I cannot tell from the descriptions you posted so far how you know where to start looking for your source at. There must be a method by which you identify where the data is located at. If this were a worksheet formula using INDIRECT, how would you specify the arguments to the INDIRECT function?

--
Rick (MVP - Excel)


"Ken G." wrote in message ...
I must be missing something here. Doesn't your code below assume that we
already know the location of the source range? I don't know, and that's why I
used the "Indirect" function in Excel to work out where the source range
starts and ends. If time permits I'll see if I can re-create my problem in a
sheet that I can post or send to you. In the meantime I've overcome it by
hard-coding the data into place for the most likely scenario. This removes
the flexibilty of selecting the pre-payment period but it'll do for now.

I appreciate your time and effort in trying to assist me here. Thanks.

"Rick Rothstein" wrote:

Let's see if I can give you a general answer. Here I will assume everything is in variables (you will have to translate them into what you actually have); I'll use the word "source" and "copy" in the variable names to keep the two locations separate.

With Worksheets(SourceSheetName)
.Range(.Cells(SourceStartRow, SourceStartColumn), ...Cells(CopyTopLeftCellRow, CopyTopLeftCellColumn)).Copy _
Worksheets(CopySheetName).Cells(CopyTopLeftCellRow , CopyTopLeftCellColumn)
End With

Pay attention to the leading "dots" in front of the Range and Cells references in the first part of the statement... they make them reference back to the worksheet that is the object of the With statement. Note that I used line continuation characters (the space/underbar at the end of each line) to prevent your newsreader from breaking up the statement between the With and End With in awkward ways, but, if you remove the line continuation characters, that is a single-line statement. Here is the same code with the variable names reduced to just their uppercase letters in order to shorten the code enough not to need the line continuations (if it still looks wrapped, widen your newsreader window and it should "unwrap")...

With Worksheets(SSN)
.Range(.Cells(SSR, SSC), .Cells(CTLCR, CTLCC)).Copy Worksheets(CSN).Cells(CTLCR, CTLCC)
End With

When you Dim your variables... the sheet names should be String, the rows should be Long and the columns should be Variant (they can be designated as either a number or letter(s) depending on how you designated the columns; that is, for Column E, as an example, you can use either 5 or "E" in the variable).

--
Rick (MVP - Excel)


"Ken G." wrote in message ...
Thanks once more. There's not much I can do other than simplify the problem.
Its a complex workbook of 14 worksheets and I don't think I could explain it
well enough without sending or posting the whole workbook (which I can't do).
Both the source data range, and the target range are determined by values in
other worksheets, and I've used the indirect function to successfully select
the source data, but couldn't figure out a formula to select the target
range, and I was hoping there'd be a similar function to "indirect" in VB,
but since there's not, its back to the drawing board ....

Thanks anyway.

"Rick Rothstein" wrote:

Don't "simplify"... it doesn't really help. First off, you don't need to use
the Indirect function, mainly because VB doesn't have one. In order for us
to be able to help you, you need to tell us where your source data is, how
you determine what part of it you want and how you determine where to put it
(all of that in detail). We will be more than happy to show you how do
accomplish what you want, but you need to give us the details so that we can
figure out what you are simply taking for granted that you know.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
Thanks Rick. The reason for using indirect is that the cell where the
range
paste will start depends on the results of other calculations. To simplify
it, I have a range of monthly loan repayments. These are to be pasted into
another worksheet, but the first cell of the target range is determined by
a
number of months by which the first loan repayment is deferred. Could be
3,
4, 6 etc. - its a variable, and that's why I'm trying to use the indirect
function so that I can offset the target range by the number of deferred
months. I know how it works in excel, I don't know how (if) it works in
VBA.

"Rick Rothstein" wrote:

Why do you think you need to use the Indirect function to do this? Using
sample ranges and sheet names (because you didn't tell us what you
actually
are working with), this single line of code...

Worksheets("Sheet1").Range("A1:F99").Copy
Worksheets("Sheet2").Range("C3")

will copy A1:F99 on Sheet1 (with its formatting) to Sheet2 starting at
cell
C3.

--
Rick (MVP - Excel)


"Ken G." wrote in message
...
I need to copy a range from one worksheet and paste the values into
another
sheet within the same workbook, but I need to use the Excel "Indirect"
function to paste it into the correct position. How is this done with a
macro? (Excel 2002)