Active Sheet, a must to copy and paste?

[Alive]

Hi,

A very stupid but annoying question. I am just copying a cell to another. But it works only when Sheet1 was the active one, or an error happens. I didn't get it. Did I type in something wrong? Thanks in advance.


Sub test()

For xx = 1 To 5

Workbooks("Circular v2.xlsx").Sheets("Sheet1").Range(Cells((1 + xx), 1), Cells((1 + xx), 1)).Copy _
Workbooks("Circular v2.xlsx").Sheets("Sheet2").Range("F20")

Next xx

End Sub

 

[Cindy Ellis]

Hello and welcome to Mr. Excel!

Although it makes sense to us humans that the workbooks() and sheets() qualifiers to Range(cells() would apply to the next instance of cells, since it's inside the same parentheses, Excel doesn't see it that way.
The best way I've seen to overcome that is to use a With...End With structure. Here's a working modification to your code:

Sub test()
For xx = 1 To 5
With Workbooks("Circular v2.xlsx").Sheets("Sheet1")
    .Range(.Cells((1 + xx), 1), .Cells((1 + xx), 1)).Copy _
        Workbooks("Circular v2.xlsx").Sheets("Sheet2").Range("F20")
End With
Next xx
End Sub

Notice that there's a "." before each of the cells, which tells Excel to use the info in the "with workbooks" statement when evaluating the .Cells reference.

edit: Note that if you use .xlsx as shown in the above code, you can't save the macro in the workbook.

Hope that helps,

Cindy

 

[AlphaFrog]

Each Cells() function has to have a Sheet reference or they default to the Active sheet.

You could do something like this...

Workbooks("Circular v2.xlsx").Sheets("Sheet1").Range([COLOR="Red"]Workbooks("Circular v2.xlsx").Sheets("Sheet1").[/COLOR]Cells((1 + xx), 1), [COLOR="Red"]Workbooks("Circular v2.xlsx").Sheets("Sheet1").[/COLOR]Cells((1 + xx), 1)).Copy _

Or this...

Sub test()
    For xx = 1 To 5
        With Workbooks("Circular v2.xlsx").Sheets("Sheet1")
            .Range(.Cells((1 + xx), 1), .Cells((1 + xx), 1)).Copy _
                Workbooks("Circular v2.xlsx").Sheets("Sheet2").Range("F20")
        End With
    Next xx
End Sub

You're code does copy five times to the same destination (F20). That is a different issue though.

 

[jim may]

Great topic -- there's not enough said about this to beginners of VBA - so thanks Cindy

only to ADD to the thread..
The culprit in the OP's line one code is (IN RED BELOW)

Workbooks("Circular v2.xlsx").Sheets("Sheet1").Range(Cells((1 + xx), 1), Cells((1 + xx), 1)).Copy
Right?

and also, not sure I'm fully understanding (?) your comment:
edit: Note that if you use .xlsx as shown in the above code, you can't save the macro in the workbook. Could you clarify?

Jim

 

[Norie]

If you are just copying one cell.

Workbooks("Circular v2.xlsx").Sheets("Sheet1").Cells(1 + xx, 1).Copy _
Workbooks("Circular v2.xlsx").Sheets("Sheet2").Range("F20")

 

[Cindy Ellis]

The culprit in the OP's line one code is (IN RED BELOW)

Workbooks("Circular v2.xlsx").Sheets("Sheet1").Range(Cells((1 + xx), 1), Cells((1 + xx), 1)).Copy
Right?

Right...each instance of Cells() has to be fully qualified. This is so non-intuitive that I still make this mistake more often than I should.

"Note that if you use .xlsx as shown in the above code, you can't save the macro in the workbook." Could you clarify?

Excel 2007 (and later versions, I presume) will not allow macros to be saved in a standard Excel workbook. It has to be either Excel Macro-enabled workbook (".xlsm") or Excel binary workbook (".xlsb"). The example code used an ".xlsx" filename, which I copied/pasted intact to a test workbook to verify that the code changes I proposed actually worked. When I saved that workbook, I was reminded that I couldn't save a macro in that type workbook. If that was really the code being used, it seemed that it could save another error message to the OP by mentioning that ahead of time

 

[jim may]

Thanks,

Jim

 

[Alive]

Hi Cindy,

Thanks!! It totally works now. I tried With-End with yesterday but got the same problem as I ignored the dot before cells. Thank you for your kindness!!

Hello and welcome to Mr. Excel!

Although it makes sense to us humans that the workbooks() and sheets() qualifiers to Range(cells() would apply to the next instance of cells, since it's inside the same parentheses, Excel doesn't see it that way.
The best way I've seen to overcome that is to use a With...End With structure. Here's a working modification to your code:

Sub test()
For xx = 1 To 5
With Workbooks("Circular v2.xlsx").Sheets("Sheet1")
    .Range(.Cells((1 + xx), 1), .Cells((1 + xx), 1)).Copy _
        Workbooks("Circular v2.xlsx").Sheets("Sheet2").Range("F20")
End With
Next xx
End Sub

Notice that there's a "." before each of the cells, which tells Excel to use the info in the "with workbooks" statement when evaluating the .Cells reference.

edit: Note that if you use .xlsx as shown in the above code, you can't save the macro in the workbook.

Hope that helps,

Cindy

 

[Cindy Ellis]

Thanks for the feedback.
Best of luck with your project,
Cindy

 

[Alive]

Thanks!! I tried both and feels myself much less stupid now. I couldn't believe I had trouble in just copying something....

Yes it copied 5 times to the same cell, this is just the first step I was planning to do and I stuck there... Thanks a lot!

Each Cells() function has to have a Sheet reference or they default to the Active sheet.

You could do something like this...

Workbooks("Circular v2.xlsx").Sheets("Sheet1").Range([COLOR="Red"]Workbooks("Circular v2.xlsx").Sheets("Sheet1").[/COLOR]Cells((1 + xx), 1), [COLOR="Red"]Workbooks("Circular v2.xlsx").Sheets("Sheet1").[/COLOR]Cells((1 + xx), 1)).Copy _

Or this...

Sub test()
    For xx = 1 To 5
        With Workbooks("Circular v2.xlsx").Sheets("Sheet1")
            .Range(.Cells((1 + xx), 1), .Cells((1 + xx), 1)).Copy _
                Workbooks("Circular v2.xlsx").Sheets("Sheet2").Range("F20")
        End With
    Next xx
End Sub

You're code does copy five times to the same destination (F20). That is a different issue though.