404 error when trying to run the below

[vbanovice123]

Hi,

Do you know if this is a system error related to an incorrect filename or filepath?


'Public Function copy_range(sheet, rowStart, columnStart, row_count, columnCount, slide, aheight, awidth, atop, aleft)
'Public Function copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
Public Function copy_range_sheet(sheet, rngname)
Sheets(sheet).Select
'Cells(rowStart, columnStart).Resize(row_count, columnCount).Select
Range(rngname).Select
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Range(rngname).Activate
Selection.Copy
Workbooks.Open Filename:= _
"F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
Sheets("HFI").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWorkbook.SaveAs Filename:="F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
Workbooks("One Pagers_13Month ViewHardCopy.xls").Close SaveChanges:=False

End If

End Function

 

[vbanovice123]

I took out some lines of code and the below now works fine.

'Public Function copy_range(sheet, rowStart, columnStart, row_count, columnCount, slide, aheight, awidth, atop, aleft)
'Public Function copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
Public Function copy_range_sheet(sheet, rngname)
Sheets(sheet).Select
'Cells(rowStart, columnStart).Resize(row_count, columnCount).Select
Range(rngname).Select
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Range(rngname).Activate
Selection.Copy
Workbooks.Open Filename:= _
"F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
Sheets("HFI").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End If
End Function

 

[JackDanIce]

Hi,

Are you using Excel 2007 or 2011? If so, I've highlighted section near the end which I think is where your problem is. There is also redundant code below that isn't doing much so I've removed it:

Public Function copy_range_sheet(sheet, rngname)
Sheets(sheet).Select
Range(rngname).Select

If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
        "No Range Selected"
Else
    Range(rngname).Copy
    Workbooks.Open Filename:= _
    "F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
    Sheets("HFI").Select
    Range("B1").PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Close savechanges:=True
    ActiveWorkbook.SaveAs Filename:="F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsm", FileFormat:=52
    Workbooks("One Pagers_13Month ViewHardCopy.xls").Close savechanges:=False
End If

End Function

Latest versions of Excel require the file format to be specified when using .SaveAs or it's better to specify it than not anyway...

 

[JackDanIce]

It looks like you took out the ActiveWorkbook.SaveAs lines which is the same part that I highlighted where I thought the error may have been caused. As it is, the second code posting could be reduced down to:

Public Function copy_range_sheet(sheet, rngname)

Sheets(sheet).Select
Range(rngname).Select

If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
    "No Range Selected"
Else
    Range(rngname).Copy
    Workbooks.Open Filename:= _
    "F:\Focus\AnitaPradeep\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
    Sheets("HFI").Select
Range("B1").PasteSpecial Paste:=xlPasteValues
    ActiveWorkbook.Close savechanges:=True
End If
End Function

 

[vbanovice123]

Thanks, I will try the above code and rerun as well.

One more thing. Can we have three modules with three different name used in the same workbook and calling the same Public function?

I get an error.

example if I have two modules:

CopyTo13MHardCopy and CopyToYearQtrHardCopy. Both modules use the same code below, the only difference being the filename in the filepath has different .xlsm name.

Can I qualify the name if I still need to use the same function and if so how?



'Public Function copy_range(sheet, rowStart, columnStart, row_count, columnCount, slide, aheight, awidth, atop, aleft)
'Public Function copy_range(sheet, rngname, slide, aheight, awidth, atop, aleft, vscale)
Public Function copy_range_sheet(sheet, rngname)
Sheets(sheet).Select
'Cells(rowStart, columnStart).Resize(row_count, columnCount).Select
Range(rngname).Select
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
Range(rngname).Activate
Selection.Copy
Workbooks.Open Filename:= _
"F:\Focus\MyFolder\Copy Paste Project\OnePagers_Templates\One Pagers_13Month ViewHardCopy.xlsx"
Sheets("HFI").Select
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

End If
End Function

 

[JackDanIce]

If it's a new topic, you should be posting it as a new thread, as per the forum's suggested guidelines