VBA in Excel

[SiameseCat]

I am trying to copy a cell or range of cells from one worksheet to another. In doing so, I used Range(cell name).Select, then Selection.Copy. Afterward, I activated a new workbook, then selected a sheet, and finally attempted to select the range that I wanted to copy to on the destination sheet. My macro code follows:
Private Sub cmd_Copy_Click()
Dim wksheet As String
wksheet = InputBox("Please enter the name of the worksheet where you want to copy the data:", "Alpha-Liberty Company&quot
Sheets("Rice Lake&quot.Select
Range("A12&quot.Select
Selection.Copy
Windows("WeightCert.XLT&quot.Activate
Sheets(wksheet).Select
Range("A7:A7&quot.Select

End Sub

Upon executing the code I get error 1004: "Select method of Range class failed." "Rice Lake" is the source sheet, and the user inputs the destination sheet.

Anybody want to offer a suggestion as to what to do? I even tried to record a macro to do this, and I got the same message.

Paul Dadosky

 

[Zathras]

I can't reproduce your error. Are you sure you are spelling the workbook and sheet names correctly? Even then I can't get Select Method failure, I just get subscript out of range.

BTW, to finish up the macro, just add these two lines at the bottom:

    ActiveCell.PasteSpecial xlPasteAll
    Application.CutCopyMode = False

 

[Bowers74]

Hi SiameseCat,

I WAS able to reproduce your error.

Try not to believe that everything you get from the Macro Recorder is correct.

Just a couple of tips:

1. You don't need to select (or even activate) anything to copy and paste.

2. Try not to ever reference a workbook with Window().Activate or .Select. Use Workbook() instead.

There are plenty of "dos" and "don'ts" dealing with the Macro Recorder, but I'm not going to get into those here.

Below you will find an "optimized" version of your macro. Please not that nothig is selected or activated in this code. When you run it, you will see that it works exactly as you want it to.

Private Sub cmd_Copy_Click()
    Dim wksheet As String
    On Error Resume Next
    wksheet = InputBox("Please enter the name of the worksheet where you want to copy the data:", "Alpha-Liberty Company")
    Sheets("Rice Lake").Range("A12").Copy Destination:= _
        Workbooks("WeightCert.XLT").Sheets(wksheet).Range("A7")
End Sub

I added the line On Error Resume Next, because I was getting an error (with your code and mine) after pressing the cancel button in the InputBox. This will take care of that.

If you want the user to see the workbook after this is done, then you can add some .Select methods:

Workbooks("WeightCert.XLT").Select
Sheets(wksheet).Select

I hope this helps!




Peace!

Mike

Never say Never!!!
Nothing is impossible!!!