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

	A	B	C	D	E
1	100	400	700	1000	1300
2	200	500	800	1100	1400
3	300	600	900	1200	1500

<tbody>
</tbody>

SheetB

	A	B
1	A1
2	D2

<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

 

[francozzy]

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

with your original code above, i still got the same issue
so what i have to do fix this error ?

 

[My Aswer Is This]

You need to answer this question

show me exactly what you have in Sheets(2)
range A1 and Range A2​

 

[francozzy]

Sorry my mistake,

unconsciously , i put some button inside sheet (2) by accident,
it's make that error

Thank you

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Sorry my mistake,

unconsciously , i put some button inside sheet (2) by accident,
it's make that error

Thank you

 

[francozzy]

What if in the first row of Sheet(1) , i put some index that reference to number of date in one month
like below

	A	B	C	D	E	F
1	1	2	3	4	5	6
2	100	400	700	1000	1300	1600
3	200	500	800	1100	1400	1700
4	300	600	900	1200	1500	1800

<tbody>
</tbody>

and then i need to copy to Sheet(3) only column for previous day , and current day and 1 next day
so, let say today 4 april, then i need copy only column with date 3th till 5th
each Sheet like Sheet(3) will represent 1 month

Is it possible ?

 

[francozzy]

so what i have to do ?

 

[My Aswer Is This]

so what i have to do ?

So now you have changed your request. I will have to see what your new needs are and see what I can do.

 

[My Aswer Is This]

When you say copy to sheet(3) you have to tell me where to paste on sheet 3

 

[My Aswer Is This]

Try this:

Sub Copy_Dates()
'Modified 4-4-18 6:35 AM EDT
ans = Weekday(Date)
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
Dim SearchString As String
Dim SearchRange As Range
SearchString = ans
Dim Lastrow As Long
Set SearchRange = Range(Cells(1, 1), Cells(1, LastColumn)).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox "That day  " & ans & "  Cannot be found" & vbNewLine & " I will Stop the script": Exit Sub
Lastrow = Cells(Rows.Count, SearchRange.Column).End(xlUp).Row
LastColumn = Sheets(3).Cells(1, Columns.Count).End(xlToLeft).Column + 1
SearchRange.Offset(, -1).Resize(Lastrow, 3).Copy Sheets(3).Cells(1, LastColumn)
End Sub

 

[francozzy]

So now you have changed your request. I will have to see what your new needs are and see what I can do.

OK. I really appreciate for your help

i'll wait your suggestion.