VBA Copy Range with Variables...

[JonW]

Hey all,

Been pounding my head over this one (Win7/MSO2010).

Basically, I'm trying to select a range of cells from one sheet and copy to another sheet using VBA. The sticky part is that I'm copying a range of cells using a For/Next loop and identifying the cells on the destination sheet using a separate loop. All connected to a button.

The trouble comes in when I use a Macro enabled button vs an ActiveX button. The range is selected normally using the Macro button, but the ActiveX button cannot get past the range select (not sure why). Also, it simply will NOT paste to the destination cells; the macro will select the cells and error out.

I've simplified the two scripts down while trying to maintain the gist of my problem.
1st, the ActiveX command:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Select
For x = 1 To 10
Sheets("Sheet1").Range(Cells(1, 5), Cells(x, 5)).Select
Selection.Copy
Next x
Sheets("Sheet2").Select
Sheets("Sheet2").Range(Cells(1, 5), Cells(x, 5)).Select
ActiveSheet.PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False
End Sub

This fails at "Sheets("Sheet2").Range(Cells(1, 5), Cells(x, 5)).Select"
I actually don't even want to select the WHOLE 'destination (Sheet2) range and would like to reduce it down to "Sheets("Sheet2").Range(Cells(1, 5)).Select", but even that fails.

The macro code is almost exactly the same:
Sub CommandButton1_Click()
Sheets("Sheet1").Select
For x = 1 To 10
Sheets("Sheet1").Range(Cells(1, 5), Cells(x, 5)).Select
Selection.Copy
Next x
Sheets("Sheet2").Select
Sheets("Sheet2").Range(Cells(1, 5), Cells(x, 5)).Select
ActiveSheet.PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False
End Sub

and it fails at "ActiveSheet.PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False" and also doesn't like a single cell destination "Sheets("Sheet2").Range(Cells(1, 5)).Select"

I need to use variables in the Range lookup because I've tailored the 1st part of this process to 'look' for specific items and log the locations for use later. Once it's determined the location of all the items that I need, I want it to copy the data under them and paste them into a separate sheet (basically cherry picking data from one sheet and compiling a new sheet).

The differences between the ActiveX and Macro functions has be perplexed. I didn't realize that the two would not run simple tasks in a similar manner. Also, why it 'dies' on 'Paste' is also a mystery.

Any suggestions would be greatly appreciated.

Also (as a side note), I read on an older post that you need to put the 'sheet("sheet").select' in front of the 'sheet("sheet").range...' because excel sometimes gets confused when you switch sheets often and the extra location reminder helps keep it on track. This seems like more code than necessary (I also don't like using 'sheet("sheet")' and rename my sheets so that I can refer to them directly), but it also seems to be the little trick that get's it back on track.

Best!

 

[Andrew Poulsom]

Welcome to MrExcel.

Try it without selecting, which is rarely necessary. Untested:

Private Sub CommandButton1_Click()
    Dim x As Long
    For x = 1 To 10
        With Sheets("Sheet1")
            .Range(.Cells(1, 5), .Cells(x, 5)).Copy
        End With
        With Sheets("Sheet2")
            .Range(.Cells(1, 5), .Cells(x, 5)).PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False
        End With
    Next x
End Sub

Your code was failing because you hadn't qualified the Cells property with its worksheet.

 

[JonW]

perfect... thank you for the quick turn around.

Please note the code works just like I wanted; however, I did miss-spell 'transpost' in my original code that was brought forward into the new code. Once corrected to 'transpose' it's works beautifully.

All that I and I learned something to boot, thank you.

 

[Dan777]

Welcome to MrExcel.

Try it without selecting, which is rarely necessary. Untested:

Private Sub CommandButton1_Click()
    Dim x As Long
    For x = 1 To 10
        With Sheets("Sheet1")
            .Range(.Cells(1, 5), .Cells(x, 5)).Copy
        End With
        With Sheets("Sheet2")
            .Range(.Cells(1, 5), .Cells(x, 5)).PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False
        End With
    Next x
End Sub

Your code was failing because you hadn't qualified the Cells property with its worksheet.

?what am I doing wrong?
Dim wsWriteTo As Worksheet

Set wsWriteTo = ThisWorkbook.Sheets("sheet1")
i=7
With wsWriteTo.Range(.Cells(20, 8 + i), .Cells(20, 8 + 6 + i)).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16777164
.TintAndShade = 0
.PatternTintAndShade = 0
End With

I receive the error "invalid or unqualified"
---it highlights ".Cells("

 

[Andrew Poulsom]

You need to do it like this:

With wsWriteTo
    With .Range(.Cells(20, 8 + i), .Cells(20, 8 + 6 + i)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16777164
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With

 

[Dan777]

You need to do it like this:

With wsWriteTo
    With .Range(.Cells(20, 8 + i), .Cells(20, 8 + 6 + i)).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16777164
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With

!Thanks!

 

[BrianX]

Welcome to MrExcel.

Try it without selecting, which is rarely necessary. Untested:

Private Sub CommandButton1_Click()
    Dim x As Long
    For x = 1 To 10
        With Sheets("Sheet1")
            .Range(.Cells(1, 5), .Cells(x, 5)).Copy
        End With
        With Sheets("Sheet2")
            .Range(.Cells(1, 5), .Cells(x, 5)).PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False
        End With
    Next x
End Sub

Your code was failing because you hadn't qualified the Cells property with its worksheet.

I am doing something similar where I need to use a variable inside the range. Here is my code:


Worksheets("Execute").Select

For O = numRowsRCost To 5 Step -1
If pnFromShip = Cells(O, 1) Then
' If the PN from Shipments is found on the execute sheet, then
With Sheets("Shipments")
.Range(.Cells(O, 1), .Cells(O, 2)).Copy
End With
' Copy the PN and the cost

With Sheets("R-Part Misses")
.Range(.Cells(pasteRow, 1), .Cells(pasteRow, 2)).PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:=False, Transpost:=False
End With


pasteRow = pasteRow + 1


End If

Next O




Now I havent finished it, but so far I am getting the run time error 1004: Application-defined or object defined error.
Do you know why I would get this error?

Thanks!
Brian

 

[Andrew Poulsom]

This:

Transpost:=False

should be:

Transpose:=False