Copy Paste Macro

[xiao85yu]

Ok,

I have 3 columns in worksheet("Count")

That has

Value1 Value2 Integer1
Value3 Value4 Integer2
Value5 Value6 Integer3

I want to write a macro that would copy and paste:

"Value1 Value2" into worksheet("Output") Integer1 times
"Value3 Value4" into worksheet("Output") Integer2 times

So for example if my worksheet("Count") has:

Hello World 2
Good Bye 3

I would want my worksheet("Count") to have (starting at cell A2):

Hello World
Hello World
Good Bye
Good Bye
Good Bye

=====================================================

I'm pretty sure this is a simple for... next loop, but i'm having problems with the syntax ...

Also, is there a way for me to keep track of the column/row counters in matrix form so I can advance the counters if I wanted to move to the adjacent cell?

Thanks much!

[mudraker]

you have not said what columns your data is in. I have assumed columns A to C

You have not said what row it starts in. I have assumed row 2

this macro will replace contents of columns A to C as required.
It works on the active sheet

Please Login or Register  to view this content.

[Richard Buttrey]

Ok,

I have 3 columns in worksheet("Count")

That has

Value1 Value2 Integer1
Value3 Value4 Integer2
Value5 Value6 Integer3

I want to write a macro that would copy and paste:

"Value1 Value2" into worksheet("Output") Integer1 times
"Value3 Value4" into worksheet("Output") Integer2 times

So for example if my worksheet("Count") has:

Hello World 2
Good Bye 3

I would want my worksheet("Count") to have (starting at cell A2):

Hello World
Hello World
Good Bye
Good Bye
Good Bye

=====================================================

I'm pretty sure this is a simple for... next loop, but i'm having problems with the syntax ...

Also, is there a way for me to keep track of the column/row counters in matrix form so I can advance the counters if I wanted to move to the adjacent cell?

Thanks much!

With your example data starting in A1 on the "Count" sheet, something like the following should do it:

Please Login or Register  to view this content.

HTH

[xiao85yu]

So I actually figured out what i needed to do, but my method is a bit "dumb" so I'd like to understand what your macro did Richard.

Sub RoleName()

Dim counter As Integer
Dim cellcounter As String

Dim oldcounter As Integer
Dim RowCount As Integer

RowCount = 2
Sheets("Output").Select
Range("A2").Select
Sheets("Count").Select
counter = Cells(RowCount, 3).Value

For j = 0 To 118
Range(Cells(RowCount, 1), Cells(RowCount, 2)).Select
Selection.Copy
Sheets("Output").Select

For i = 0 To counter
ActiveSheet.Paste
Cells(RowCount + i + oldcounter, 1).Select
Next

RowCount = RowCount + 1
oldcounter = counter + oldcounter
Sheets("Count").Select
counter = Cells(RowCount, 3).Value - 1
Next

End Sub

That was my macro, and when I ran your macro, it appended the two columns together with a " " and also the count was a bit off. Here's my 3 columns with data:

|Team| |Role Name| |Counts|
FI General Ledger Master Data Approver 3
FI General Ledger Master Data Maintainer 4
FI General Ledger Master Data Reviewer 3

So my macro gives me the two column output:
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer

And yours gives:
Team Role Name
Team Role Name
Team Role Name
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer

So the count gets flipfloped and there's extra Team Role Name, I just didn't understand how the indexing works, if i did i could probably just tweak yours to make it look "smarter" :-p

[Richard Buttrey]

So I actually figured out what i needed to do, but my method is a bit "dumb" so I'd like to understand what your macro did Richard.


That was my macro, and when I ran your macro, it appended the two columns together with a " " and also the count was a bit off. Here's my 3 columns with data:

|Team| |Role Name| |Counts|
FI General Ledger Master Data Approver 3
FI General Ledger Master Data Maintainer 4
FI General Ledger Master Data Reviewer 3

So my macro gives me the two column output:
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer

And yours gives:
Team Role Name
Team Role Name
Team Role Name
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Approver
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Maintainer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer
FI General Ledger Master Data Reviewer

So the count gets flipfloped and there's extra Team Role Name, I just didn't understand how the indexing works, if i did i could probably just tweak yours to make it look "smarter" :-p

Hi,

I had assumed you wanted the first two columns concatenated together, hence the single column output. Also I had not got any Field labels in my test data. Since these exist in row 1, the macro needs to start with row 2 data. Subsequently I also realised that my code would only run if the Active sheets was the "Count" sheet. There's a modification below which allows it to be run from any sheet, plus corrections for your other points.

Please Login or Register  to view this content.

Rgds

[mudraker]

xiao85yu

Please read forum rules & wrap your VBA code

A thread with the rules is available at the top of each forum or see links below

If you do not understand the VBA code wrap instructions have a look at my last reply in this thread
http://www.excelforum.com/showthread.php?t=583950

[xiao85yu]

Worksheets("Output").Range("A65536").End(xlUp).Offset(1, 0) _
= Worksheets("Count").Range("A2").Cells(x, 1)
Worksheets("Output").Range("A65536").End(xlUp).Offset(0, 1) _
= Worksheets("Count").Range("A2").Cells(x, 2)

Thanks! Could you explain what those two lines do? I understood the x and y for loops, basically to advance down each row and y goes to the value in the 3rd column (count).

I'm have a bit of a problem understanding the Range("A65536").End(xlUp).Offset(0, 1)

[Richard Buttrey]

Thanks! Could you explain what those two lines do? I understood the x and y for loops, basically to advance down each row and y goes to the value in the 3rd column (count).

I'm have a bit of a problem understanding the Range("A65536").End(xlUp).Offset(0, 1)

Hi,

Yes, it's a fairly standard method of finding the very last cell in a column. Using the cursor to find the last cell one often positions it at the top of a column and enters END Down(arrow) from the keyboard. That's fine if you can guarantee there are no gaps in the data for that particular column. If there are it simply finds the cell above the first gap, and there are still records below that.

In this case the task was to add data underneath the previous data, and the safest way of identifying the last available cell is to start at the very last row - 65536 (unless you're using XL2007), and perform an END UP (arrow). This finds the last cell, and the .Offset(1,0) positions the cursor in the next blank cell underneath. The .End(xlup) is simply the VBA code for the End UP(arrow) key combination.

HTH