Copy Selected range to another sheet with define range within cell

F

francozzy

New Member
Joined
Apr 3, 2018
Messages
26
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
ABCDE
110040070010001300
220050080011001400
330060090012001500

<tbody>
</tbody>

SheetB
AB
1A1
2D2

<tbody>
</tbody>


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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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:

Code: 
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
 
Upvote 0
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?
 
Upvote 0
My Aswer Is This said:
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
Click to expand...


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.

My Aswer Is This said:
Try this:

Code: 
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
Click to expand...

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

Code: 
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.
 
Upvote 0
My Aswer Is This said:
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?
Click to expand...

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

Thank you for your response

My Aswer Is This said:
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
Click to expand...

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

My Aswer Is This said:
Try this:

Code: 
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
Click to expand...

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
 
Upvote 0
Nope, i got message like below

image.png
[/URL][/IMG]
 
Upvote 0
Make sure all your sheet names are correct.
show me exactly what you have in Sheets(2)
range A1 and Range A2
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
214
jmk15315
J
E
  • Question
VB Help with code to copy selected rows to specified cells in another sheet.
2
Replies
12
Views
396
Excel_Assis
E
A
  • Solved
Search a range of cells in another tab and return a value
Replies
6
Views
555
Peter_SSs
Peter_SSs
C
  • Question
Using the Find method to copy 4 cell values above Find = X to another sheet for every value of X found in a column
Replies
6
Views
350
chazrab
C
J
  • Solved
Need to sum the bottom 5 cells that have values....
2
Replies
14
Views
823
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,971
Messages
6,122,521
Members
449,088
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top