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

 

[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

[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]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/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]
[TD="align: center"]1600[/TD]
[/TR]
[TR]
[TD="align: center"]3[/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]
[TD="align: center"]1700[/TD]
[/TR]
[TR]
[TD="align: center"]4[/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]
[TD="align: center"]1800
[/TD]
[/TR]
</tbody>[/TABLE]


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.