I want to select a range of columns from a workbook I have no formatting contorl over and copy them to another workbook sheet for analysis.
The code below is a test code and so far it selects the highest and lowest numbers representing a date of a month (i.e. 1, 2, 3, ... 30) depending how many days there are each month. Then it correlates the cell addresses and column numbers based upon those minimum and maximum values so I caputure the whole month. Now I want to correlate column numbers, then select those columns and the range of columns in between to copy the data.
I was able to return the column numbers for the min and max values and thought I could do
Columns(scol:lcol).copy
and it would work, but it doesn't. I've tried a number of tactics and help menu suggestions and no success. Maybe I have to return column letters, but I haven't found logical code for that.
This is my test code. It
The code below is a test code and so far it selects the highest and lowest numbers representing a date of a month (i.e. 1, 2, 3, ... 30) depending how many days there are each month. Then it correlates the cell addresses and column numbers based upon those minimum and maximum values so I caputure the whole month. Now I want to correlate column numbers, then select those columns and the range of columns in between to copy the data.
I was able to return the column numbers for the min and max values and thought I could do
Columns(scol:lcol).copy
and it would work, but it doesn't. I've tried a number of tactics and help menu suggestions and no success. Maybe I have to return column letters, but I haven't found logical code for that.
This is my test code. It
Code:
Sub FindSmLgnmAdr()
Dim lgnm As Integer
Dim lrng As Range
Dim ladr As String
Dim lcol As String
Dim smnm As Integer
Dim srng As Range
Dim sadr As String
Dim scol As Integer
Dim colnm As Integer
'Unmerge cells for copying
Range("E1:G2").Select
With Selection
.MergeCells = False
End With
'Finds number of days in month and copies data range
lgnm = WorksheetFunction.Max(Range("3:3").Value)
smnm = WorksheetFunction.Min(Range("3:3").Value)
Set lrng = Range("3:3").Find(lgnm)
Set srng = Range("3:3").Find(smnm)
sadr = srng.Address
scol = Range(srng.Address).Column
ladr = lrng.Address
lcol = Range(lrng.Address).Column
Range("E1:G2").Select
With Selection
.MergeCells = True
End With
MsgBox smnm & " = " & sadr & " = " & scol & vbNewLine & _
lgnm & " = " & ladr & " = " & lcol
End Sub