View Single Post
 
Old 07-28-2011, 06:49 AM
Catalin.B Catalin.B is offline Windows Vista Office 2007
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
File Type: xlsm example.xlsm (18.6 KB, 3 views)
Reply With Quote