Take a look at the example worksheet, see how it works.
The macro depends on Cells B5 si B6 marked in yellow for column number and worksheet to copy from, you can change them as you need. The macro looks like this:

Sub SchimbaCap2()
Dim col As Range, row As Range, Myrange As Range
Set col = Worksheets("Hoja1").Range("B1")

If Worksheets("Hoja1").Range("B2") = 0 Then
MsgBox " Type in cell B6 the name of the source worksheet!"
Exit Sub
End If

Set Myrange = Range(Worksheets(Worksheets("Hoja1").Range("B2").V alue).Cells(5, col), Worksheets(Worksheets("Hoja1").Range("B2").Value). Cells(15, col))
Myrange.Copy
Worksheets("Hoja3").Range("A5").PasteSpecial (xlPasteValues)

Application.CutCopyMode = False
Worksheets("Hoja3").Activate
On Error GoTo 0

End Sub

Attached Files

example.xlsm (18.6 KB, 11 views)