VBA Copy and Paste Not Working

[drmingle]

Issue: Everything works fine with this code, but the last section which is marked not working.

Objective:To copy the value from one worksheet to the other.

Private Sub CommandButton1_Click()
Dim text As String
Dim i As Integer
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) Then
        text = text & Me.ListBox1.List(i) & vbNewLine
    End If
Next i
MsgBox "Items you selected: " & vbNewLine & text
GetSelectedItemsText = text
Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets
 
    wks.Select
    Range("G3:G9").Select
    Selection.Copy
    Sheets("SPA_SummaryVIEW").Select
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    wks.Select
    Range("O1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("SPA_SummaryVIEW").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:C").EntireColumn.AutoFit
    Range("C6").Select
    Application.CutCopyMode = False
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("C7:C12").Select
    Selection.Style = "Currency"
    Range("C4").Select

'**NOT WORKING** This section is giving me the problem. I want to copy the value from the variable wks and paste into SPA_SummaryView.
    wks.Select
    Range("O1").Select
    Selection.Copy
    Range("O8").Select
    Sheets("SPA_SummaryVIEW").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C15").Select
    Columns("C:C").EntireColumn.AutoFit
    Range("C2").Select
Next
Unload Me

End Sub

I am using Excel 2010. Any help would be appreciated...

 

[James Snyder]

First thing to do is to simplify the code to not have the selects. As an example:

wks.Range("01").Copy Destination:=Sheet("SPA_SummaryVIEW")Range("C5")

Untested, but it shows how you can condense the code to make it easier to read.

 

[drmingle]

First thing to do is to simplify the code to not have the selects. As an example:

wks.Range("01").Copy Destination:=Sheet("SPA_SummaryVIEW")Range("C5")

Untested, but it shows how you can condense the code to make it easier to read.

I changed your code to this (added an "s" and a "."):

wks.Range("01").Copy Destination:=Sheets("SPA_SummaryVIEW").Range("C5")

However, I am getting a run-time error '1004': Method 'Range of Object'_worksheet failed.

Erroring on your line.

Any ideas?

 

[par60056]

I think that it should be a letter O not a zero in range("O1")

 

[James Snyder]

I think he is right. Also, run-time error "1004" is the generic message received any time an object isn't happy. It is too bad Microsoft couldn't have designed their error checking with more context...

 

[drmingle]

I think that it should be a letter O not a zero in range("O1")

It was already the letter "O", just lower case.

I'm still getting the same error.

 

[James Snyder]

My error. I failed to notice that "wks" was undefined:

Sub Main()
    Dim wks As Worksheet
    
    Set wks = Sheets(1)
    wks.Range("O1").Copy Destination:=Sheets("SPA_SummaryVIEW").Range("C5")
End Sub

 

[drmingle]

My error. I failed to notice that "wks" was undefined:

Sub Main()
    Dim wks As Worksheet
    
    Set wks = Sheets(1)
    wks.Range("O1").Copy Destination:=Sheets("SPA_SummaryVIEW").Range("C5")
End Sub

I appreciate your persistance.

What is really strange is that I have been using the following with success:

Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets

However, I went ahead and used your code and mine together without success:

Dim wks As Worksheet
Set wks = ActiveWindow.SelectedSheets
For Each wks In ActiveWindow.SelectedSheets
 
    wks.Select
    Range("G3:G9").Select
    Selection.Copy
    Sheets("SPA_SummaryVIEW").Select
    Range("C6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    wks.Select
    Range("O1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("SPA_SummaryVIEW").Select
    Range("C5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:C").EntireColumn.AutoFit
    Range("C6").Select
    Application.CutCopyMode = False
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("C7:C12").Select
    Selection.Style = "Currency"
    Range("C4").Select

'This section is giving me the problem. I want to copy the value from the variable wks and paste into SPA_SummaryView.
    
    wks.Range("o1").Copy Destination:=Sheets("SPA_SummaryVIEW").Range("C5")
    
   ' Selection.Copy
    'Range("O8").Select
    'Sheets("SPA_SummaryVIEW").Select
    'Range("C5").Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    '    :=False, Transpose:=False
    'Range("C15").Select
    'Columns("C:C").EntireColumn.AutoFit
    'Range("C2").Select
Next

The form I am using allows me to make use of the ActiveWindow.SelectedSheets command.

However, I am running into an issue:

Run-Time Error '13': Type Mismatch

It's bugging out on this exact line: Set wks = ActiveWindow.SelectedSheets

I really appreciate your willingness to help.

 

[James Snyder]

What is really strange is that I have been using the following with success:

Dim wks As Worksheet
For Each wks In ActiveWindow.SelectedSheets

That is part of the For Each syntax. For Each is assigning the value as long as it has been Dimmed.

The form I am using allows me to make use of the ActiveWindow.SelectedSheets command.

However, I am running into an issue:

Run-Time Error '13': Type Mismatch

It's bugging out on this exact line: Set wks = ActiveWindow.SelectedSheets

I really appreciate your willingness to help.

The "Set" is used with objects. I am unfamiliar with the ActiveWindow.SelectedSheets command, but would check to make sure it is returning an object (like a worksheet, range, cell, etc).

 

[par60056]

ActiveWindow.SelectedSheets will return an array of sheets not a single sheet.

If you just want the sheet that is showing, try
set wks=ActiveWorksheet