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

 

[Alive]

Hi Jim,

Thanks for your posting, which helped me understand the codes further.

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

 

[Alive]

Thanks! Efficiency is always of importance.

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")

 

[Alive]

Thanks, also could I know where did you learn this? I am reading a book about vba programming, but it seems not help me on this issue...

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

 

[Norie]

I don't think a book would teach you this, but I'm not sure a book would actually use syntax like that to copy a single cell.

Even if it was more than one cell you can probably still use Resize (and Offset?) to get the right range rather than Range(Cells, Cells).

 

[Cindy Ellis]

Thanks, also could I know where did you learn this? I am reading a book about vba programming, but it seems not help me on this issue...

That particular bit I learned here on the forum...I browse questions and answers randomly, and try to learn from other users.
In general, I learned VBA by having a project that I really wanted to automate (being of the belief that computers are better than I am at doing repetitive tasks). I tried record and playback a few times, then went to VBA help (sometimes helpful, sometimes not) to find bits and pieces I could modify.
I eventually found the Mr. Excel forum, and often try to answer questions that I don't really know the answer to, just to learn a bit more that may come in handy for one of my own projects in the future.
Cindy

 

[Alive]

You are right, I changed the codes from the book and didn't realize the fault. Thanks.

I don't think a book would teach you this, but I'm not sure a book would actually use syntax like that to copy a single cell.

Even if it was more than one cell you can probably still use Resize (and Offset?) to get the right range rather than Range(Cells, Cells).

 

[Alive]

Thanks for sharing your valuable experience! It seems that I will come here frequently to learn more.

That particular bit I learned here on the forum...I browse questions and answers randomly, and try to learn from other users.
In general, I learned VBA by having a project that I really wanted to automate (being of the belief that computers are better than I am at doing repetitive tasks). I tried record and playback a few times, then went to VBA help (sometimes helpful, sometimes not) to find bits and pieces I could modify.
I eventually found the Mr. Excel forum, and often try to answer questions that I don't really know the answer to, just to learn a bit more that may come in handy for one of my own projects in the future.
Cindy