Copy Selected range to another sheet with define range within cell

[francozzy]

Hi,

i'm newbie on vba excel

I would like to copy some range on selected sheet to another sheet, and defined the range with value from another sheet

i'll give the illustration below :

SheetA
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]1300[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]1400[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]900[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]1500[/TD]
[/TR]
</tbody>[/TABLE]

SheetB
[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]D2[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]


I want to copy selected range from SheetA to new Worksheet SheetC,
with specific value of Range that has been define at SheetB

So what i have to do ?

thank you

 

[My Aswer Is This]

In this example I assume you want to copy the Range("A1:D2") from Sheet(1)
And paste into Range("B2") of sheet(3)

You did not say where to paste it on sheet3

I'm using Sheet(1) Sheet(2) and Sheet(3)

Normally people do not name their sheet

SheetA SheetB and SheetC

Try this:

Sub Copy_Range()
'Modified 4-4-18 1:00 AM EDT
ans = Sheets(2).Range("A1").Value
anss = Sheets(2).Range("A2").Value
Sheets(1).Range(ans & ":" & anss).Copy Sheets(3).Range("B1")
End Sub

 

[My Aswer Is This]

Was not sure what this meant:

from SheetA to new Worksheet SheetC​

Did this mean you wanted a new sheet made and paste the data into the new sheet.
If so how do you want the new sheet named. And where are we to copy the data to on the new sheet?
And are we copying just values or values and formatting?

 

[francozzy]

In this example I assume you want to copy the Range("A1:D2") from Sheet(1)
And paste into Range("B2") of sheet(3)

You did not say where to paste it on sheet3

I'm using Sheet(1) Sheet(2) and Sheet(3)

Normally people do not name their sheet

SheetA SheetB and SheetC

As you said, that normally people donot name their sheet,
but for some business purpose, i need to name it with difference name reference to my product
and for my question purpose, i name it with SheetA , SheetB ant etc.

Try this:

Sub Copy_Range()
'Modified 4-4-18 1:00 AM EDT
ans = Sheets(2).Range("A1").Value
anss = Sheets(2).Range("A2").Value
Sheets(1).Range(ans & ":" & anss).Copy Sheets(3).Range("B1")
End Sub

could you please tell me more , how if the sheet has name ?
could be the code will be like below ?

ans = Sheets(SheetB).Range("A1").Value
anss = Sheets(SheetB).Range("A2").Value
Sheets(SheetA).Range(ans & ":" & anss).Copy Sheets(SheetC).Range("B1")

Please correct me if i got wrong code.

 

[francozzy]

Was not sure what this meant:

from SheetA to new Worksheet SheetC​

Did this mean you wanted a new sheet made and paste the data into the new sheet.
If so how do you want the new sheet named. And where are we to copy the data to on the new sheet?
And are we copying just values or values and formatting?

No need to make new Sheet for the SheetC, we need only copying values

 

[My Aswer Is This]

So then my script should work. Did you try it?

 

[francozzy]

Hi,

Thank you for your response

In this example I assume you want to copy the Range("A1:D2") from Sheet(1)
And paste into Range("B2") of sheet(3)

You did not say where to paste it on sheet3

I'm using Sheet(1) Sheet(2) and Sheet(3)

Normally people do not name their sheet

SheetA SheetB and SheetC

For business purpose, i need to name all my sheet to reference all of my product
so that's why in this question i name it with SheetA, SheetB and etc

Try this:

Sub Copy_Range()
'Modified 4-4-18 1:00 AM EDT
ans = Sheets(2).Range("A1").Value
anss = Sheets(2).Range("A2").Value
Sheets(1).Range(ans & ":" & anss).Copy Sheets(3).Range("B1")
End Sub

How about we change with the sheet name that we have like SheetA, SheetB and etc ?
Could be the code will be like below ?

Sub Copy_Range()
'Modified 4-4-18 1:00 AM EDT
ans = Sheets("SheetB").Range("A1").Value
anss = Sheets("SheetB").Range("A2").Value
Sheets("SheetA").Range(ans & ":" & anss).Copy Sheets("SheetC").Range("B1")
End Sub

Is that Correct ?

Thank you

 

[My Aswer Is This]

Yes that looks like it will work.

 

[francozzy]

Nope, i got message like below

[/URL][/IMG]

 

[My Aswer Is This]

Make sure all your sheet names are correct.
show me exactly what you have in Sheets(2)
range A1 and Range A2