import cell value ( area ) to VBA.

[technician12]

Hello.

i'm looking to get my macro to select an area based on 2 cells values.

for examlple:
i want my macro to look at I3 & I4 and then select the area described in these cells, so if the contents in I3 & I4 is A1 and C46 then the macro will select A1:46.

see picture for reference.

[technician12]

snippet of current code:

Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim OutApp As Object
Dim OutMail As Object
Set Source = Nothing
On Error Resume Next
Set Source = Range("want macro to set area specified in I3 and I4 here, if possibe").SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)

[Artik]

Set Source = Range(Range("I3").Value, Range("I4").Value).SpecialCells(xlCellTypeVisible)
Artik

[technician12]

The source is not a range or the sheet is protected, please correct and try agein.

i forgot to mention.
the cells to call and get value from is on "Sheet2"
sorry for the hassel.

[Artik]

So, you have active eg Sheet1 and now you want to get the values of I3 and I4 cells from Sheet2 and create a range reference in Sheet2. If so, then:
With Worksheets("Sheet2")
Set Source = .Range(.Range("I3").Value, .Range("I4").Value).SpecialCells(xlCellTypeVisible)
End With
Pay attention to the dots before Range.

Artik

[technician12]

Sorry, agein i misrepresented my wish :(
range reference in sheet1

sheet1 - the area to be selected
sheet2 (I3 & I4 = the range to select on sheet1 )

sorry for the misunderstanding

[Artik]

In that case
With Worksheets("Sheet2")
Set Source = Worksheets("Sheet1").Range(.Range("I3").Value, .Range("I4").Value).SpecialCells(xlCellTypeVisible)
End WithGets values from Sheet2 and creates a range reference in Sheet1.

Artik