"run-time error '1004' - select method of range failed"

[maemi weirdoke]

Hello all,

I have a similar question.

Basically the macro got stuck at this point: ****


Private Sub get_holi_data_Click()

Dim sFileName As String 'gives file
Dim teller1 As Integer
Dim tmp1 As String
Dim datemonth As Integer
Dim datename As String

'Sorts out which month sheet you want
datemonth = Range("E1:E1").Value

Select Case datemonth
Case Is = 1
datename = "January"
Case Is = 2
datename = "February"
Case Is = 3
datename = "March"
Case Is = 4
datename = "April"
Case Is = 5
datename = "May"
Case Is = 6
datename = "June"
Case Is = 7
datename = "July"
Case Is = 8
datename = "August"
Case Is = 9
datename = "September"
Case Is = 10
datename = "October"
Case Is = 11
datename = "November"
Case Is = 12
datename = "December"
End Select

' Import data from holiday

sFileName = Application.GetOpenFilename("Microsoft Excel Files (*.xls),
*.xls")
If sFileName <> "False" Then

Workbooks.Open sFileName, UpdateLinks:=3
Sheets(datename).Select 'works
Range("A4:A60").Select 'stuck here ****
'Range("A4:A4").Select
'MsgBox Range("A4:A4").Select
'Range("A4:A4").Activate
'Range("A60").Activate
' Selection.Copy
Windows("test.xls").Activate 'activate this worksheet --works
Sheets("Temp").Select 'works
'Columns("A:A").Select
'Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
' True, Transpose:=False
Windows(docname).Activate 'works
ActiveWindow.Close 'works

Else
MsgBox ("Please provide a valid holiday xls. document")
End If

End Sub

As I only have "basic" knowledge of VB.net, maybe you can help me out
here?
Any help is much appreciated with of course a little explanation.

 

[Mike H]

Hi,

You code up to and including the part you say doesn't work is fine so
provided the workbook you open has a worksheet with the same name picked in
the select case statement then it selects A4 - A60 of that sheet.

After that your code becomes a bit odd in that you seem to be selecting
ranges and doing nothing. In fact it's not obvious why you select A4 - A60
because your next commented out line would select A4 so perhaps you could
explain what you would like to happen.

Mike

 

[Dave Peterson]

When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code.

And you can only select ranges on a sheet that's active. So when you open the
other workbook, it becomes the activesheet.

But when you write:
Range("A4:A60").Select

It's trying to select A4:A60 on the sheet with the button.

But you can do lots of this stuff without any activating or selecting. Usually
the resulting code is easier to understand and runs quicker, too.

I'm not sure if I translated your code correctly. I think you're opening a
workbook and copying a range (a1:a40) from the worksheet with the correct month
name.

Then pasting those values in the Temp worksheet of the workbook with the code
(and button).

If that's close, this may work:

Option Explicit
Private Sub CommandButton1_Click()
'Private Sub get_holi_data_Click()

Dim sFileName As Variant 'could be boolean false
'Dim Teller1 As Long 'I didn't use this
'Dim Tmp1 As String 'or this
Dim DateMonth As Long
Dim DateName As String
Dim OtherWkbk As Workbook
Dim RngToCopy As Range
Dim DestCell As Range
Dim InValidMonth As Boolean

'Sorts out which month sheet you want
DateMonth = Me.Range("E1").Value
InValidMonth = False
If IsNumeric(DateMonth) = False Then
InValidMonth = True
Else
If DateMonth > 12 _
Or DateMonth < 1 Then
InValidMonth = True
End If
End If

If InValidMonth = True Then
MsgBox "Please fix the value in E1!"
Exit Sub
End If

'instead of a select case:
DateName = Format(DateSerial(2008, Clng(DateMonth), 1), "MMMM")

sFileName = Application.GetOpenFilename("Microsoft Excel Files, *.xls")
If sFileName = False Then
MsgBox "Please provide a valid holiday xls. document"
Else
Set OtherWkbk = Workbooks.Open(Filename:=sFileName, UpdateLinks:=3)

Set RngToCopy = Nothing
On Error Resume Next
Set RngToCopy = OtherWkbk.Worksheets(DateName).Range("a4:a60")
On Error GoTo 0

If RngToCopy Is Nothing Then
MsgBox "Missing " & DateName & " worksheet in" & vbLf _
& OtherWkbk.Name
Else
'me is the worksheet with the button
'me.parent is the workbook that holds that worksheet
Set DestCell = Me.Parent.Worksheets("Temp").Range("a1")

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
End If

OtherWkbk.Close savechanges:=False
End If

End Sub

If I guessed wrong, it shouldn't be too difficult to modify the rngtocopy and
destcell to what you need.

Maybe the bottom of column A of the Temp Worksheet???

with me.parent.worksheets("Temp")
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with