CopyPaste function with changing range

[GerrardSVK]

Hello All,

can anyone help me? I need to make something very simmilar like code I wrote but I need to change argument F6:F7. So there will be sheet where I need to put exact range and this range will be used in this macro instead of this anytime when I change it. I was thinking about change a cell content into string and then use it in that argument can it be correct (see second code). I need to do some kind of logic like in second code.

Sub Makro4()
 
   Workbooks(2).ActiveSheet.Range("F6:F7").Copy
   Workbooks(1).Sheets(1).Range("F6:F7").PasteSpecial xlPasteValues
 
 
End Sub

Sub Makro1()

    Dim Range1 As String

    Range1 = ThisWorkbook.Range("C6").Value
    
    Dim Range2 As String

    Range2 = ThisWorkbook.Range("C5").Value
 

     Workbooks(2).Sheets(1).Range("Range1").Copy

     Workbooks(3).Sheets(1).Range("Range2").PasteSpecial xlPasteValues

 

End Sub

 

[RoryA]

Just remove the quotes round the variables:

     Workbooks(2).Sheets(1).Range(Range1).Copy

     Workbooks(3).Sheets(1).Range(Range2).PasteSpecial xlPasteValues

 

[GerrardSVK]

Just remove the quotes round the variables:

     Workbooks(2).Sheets(1).Range(Range1).Copy

     Workbooks(3).Sheets(1).Range(Range2).PasteSpecial xlPasteValueDo
[/QUOTE]

Do not work for me point is that I will have in cell C6 value for example H9 and in cell C5 there will be written H38. So If I run macro then it will copy range of cells in another workbook by written values in cells C5 and C6 So if i overwrite it from H9 to H15 it will copy range H15:H38. How to perform this task?

 

[RoryA]

I missed that you hadn't included a worksheet when getting the values - for example:

    Range1 = ThisWorkbook.Sheets("some sheet name here").Range("C6").Value