Help me complete this copy paste code

[booost]

Hi, I am very new to VBA so I need a bit of help here.
I need to copy paste a range to another work sheet. The paste location is determined by C1. In it is just text like A100, and I need the macro to use an indirect function to correctly apply it.

Please Login or Register  to view this content.

A1 needs to be changed to indirect C1. I have searched a lot online and can't find indirect being used in a macro so I have no clue how to do it. Also I would really appreciate some help cleaning up the code and putting in anything else that is neccesary for it to work.

[royUK]

Try this

Please Login or Register  to view this content.

[booost]

Try this

Please Login or Register  to view this content.

Thank you!

It will take me a minute or two to figure out how to even try this code. I tried recording a macro, opening editor on it, erasing everything and replacing with this code. But it then disappears from the macro viewer and I can't find it.

[royUK]

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

added some notes for you

[AndyPS]

You will notice that in defining the range, the value A1 is in quotes as it is text. Assuming that the value in cell C1 is text - such as A$7, you can place that in the Range() such as (expanded to make it more readable)

Please Login or Register  to view this content.

This is for illustration and I haven't tested it.
By the way, you don't need to select a range to copy it; copy can be applied to a range itself.
Pasting can be done to a range without selecting as well but you need to use PasteSpecial

Please Login or Register  to view this content.

Hope this helps.

[Domski]

You'll need to have a Sub...End Sub around it:

Please Login or Register  to view this content.

Dom

[booost]

Thanks but after I chose tools | macro| macros, I can't find it there

I tried going to compile VBA project, and it pops up with an error saying "Compile error: Invalid outside procedure" and highlights "With Worksheets("Sheet1") . I have of course no clue what that means but it might be related.

EDIT: 2 new answers above me. Thank you Domski that has to be the problem! Something I should have figured out, I just haven't looked at VBA code before. And thank you also AndyPS!

[AndyPS]

When using Worksheet().Range().Select, the worksheet specified has to be active or a VBA error occurs. This is one of the reasons it is better to copy and paste without a selection.
Edit
Whilst the code produced using the Macro Recorder can be helpful in learning the parameters needed for a method, it is very flabby and not always robust. It nearly always uses Select and cell movements to navigate whereas this is seldom necessary.

[booost]

I am now getting

"Run-time error'1004':

Application-defined or object-defined error"

With both of the codes. I'm googling the problem.

Debugger highlights this: .Range("A2:T20").Copy Worksheets("Sheet2").Range(sRng)

and this for the other code: Worksheets("Sheet2").Range(sIndirect).PasteSpecial xlPasteAll

[Domski]

Debugger highlights this: .Range("A2:T20").Copy Worksheets("Sheet2").Range(sRng)

You have both a Sheet1 and Sheet2 in your workbook and C1 on Sheet1 contains a valid range reference?

Dom

[booost]

You have both a Sheet1 and Sheet2 in your workbook and C1 on Sheet1 contains a valid range reference?

Dom

That was the problem eheh. At first I just wanted to try it quickly so I opened up 2 clean sheets and ran the code. Nothing worked of course so I figured it was because I hadn't entered values in C1. So I quickly filled in C1 with just numbers. But it works now since C1 has both a letter and numbers.

Thank you so much for the help!

I have marked this as solved.

[royUK]

When using Worksheet().Range().Select, the worksheet specified has to be active or a VBA error occurs. This is one of the reasons it is better to copy and paste without a selection.
Edit

That's wrong. The code is selecting the sheet first, except the brackets are empty. It's right that it's not necessary to select the sheet, my code doessn't.

[AndyPS]

I am pleased this has now been solved. For the sake of clarity for those referring to this thread in future,

When using Worksheets().Range().Select, the worksheet specified has to be active or a VBA error occurs

is not complete code but just an example of the structure. Values need to be put in the brackets if it is to be used.
Also the VBA error can be seen from this snippet based on at least two sheets in a workbook

Please Login or Register  to view this content.

where Run-time error 1004 'Select method of Range class failed' occurs against the last line before End Sub.
However

Please Login or Register  to view this content.

works.
When I tried booost's original partial code, putting it in a procedure, it produced the same run-time error.

[royUK]

Th whole point is taht you do not need to select the sheets or ranges to copy from or to. If I was using code as you say I would use GoTo instead