Macro to copy/paste down X times (where X is the number of rows)
Solved/Closed
JW32
TrowaD
- Posts
- 8
- Registration date
- Friday January 4, 2013
- Status
- Member
- Last seen
- February 21, 2013
TrowaD
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Related:
- Excel repeat rows a specified number of times vba
- Repeat rows in Excel: based on cell value, VBA - Guide
- Excel - Repeat rows a specified number of times ✓ - Forum - Excel
- Copy and insert rows and number of times ✓ - Forum - Excel
- How to repeat rows by x number of times ✓ - Forum - Excel
- Excel - Repeat rows to a specific number of times ✓ - Forum - Excel
10 replies
TrowaD
Updated by TrowaD on 14/03/17 at 12:07 PM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Updated by TrowaD on 14/03/17 at 12:07 PM
Hi Jusip16,
As per your sample data, the following code will do as requested.
The result will be placed in a second sheet. First sheet is called Sheet1 and the second sheet is called Sheet2. Either name your sheets like that or find those sheet references in the code and change them to match your (easily done by selecting entire code [CTRL+a] and use the find/replace window [CTRL+h]).
Here is the code:
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
As per your sample data, the following code will do as requested.
The result will be placed in a second sheet. First sheet is called Sheet1 and the second sheet is called Sheet2. Either name your sheets like that or find those sheet references in the code and change them to match your (easily done by selecting entire code [CTRL+a] and use the find/replace window [CTRL+h]).
Here is the code:
Sub RunMe() Dim x, lRow As Integer lRow = Range("A" & Rows.Count).End(xlUp).Row x = 1 Sheets("Sheet1").Select With Sheets("Sheet2") Do x = x + 1 If Cells(x, "C").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "C").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "C").Value End If If Cells(x, "D").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "D").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "D").Value End If If Cells(x, "E").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "E").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "E").Value End If If Cells(x, "F").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "F").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "F").Value End If If Cells(x, "G").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "G").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "G").Value End If If Cells(x, "H").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "H").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "H").Value End If If Cells(x, "I").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "I").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "I").Value End If If Cells(x, "J").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "J").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "J").Value End If If Cells(x, "K").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "K").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "K").Value End If If Cells(x, "L").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "L").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "L").Value End If If Cells(x, "M").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "M").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "M").Value End If If Cells(x, "N").Value <> vbNullString Then .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "A") .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Sheets("Sheet1").Cells(x, "B") .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(1, "N").Value .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = Cells(x, "N").Value End If Loop Until x = lRow End With End Sub
Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
JW32
Feb 21, 2013 at 10:46 AM
- Posts
- 8
- Registration date
- Friday January 4, 2013
- Status
- Member
- Last seen
- February 21, 2013
Feb 21, 2013 at 10:46 AM
Hi!
It is always so hard to describe things. What was clear in my mind - is obviously not clear to another!
If I can get this to work - this macro will actually be in the middle of a much larger macro that actually works (except this one important step)!
Sheet 1 has data. What shows up on Sheet 1 is a result of some look ups. Not part of a Macro at all. It is dog information. Name and Owner (and a few other bits). So the user keys in a number and up pops all the information from the database about that dog. How many lines pop up could be 1 or any number up to about 250.
The Macro I have copies these lines to another sheet. Sheet 2. Sheet 2 will have columns A to G. And as I said - an undetermined number of lines. As many lines as there are records for the dog in the database.
Sheet 2 only has the lines copied from Sheet 1. Meaning there are no lines with spaces or blanks. Only the results from page 1 are on page 2. They get copied in by my Macro.
So now we are on sheet 2. Lets for my example say we copied in 11 rows.
I need the value in E1 and F1 to be copied down 10 times (11-1). So that all 11 rows have the same value in Column E and F.
The values in the other columns can not be change.
How many times do we have to copy? The same number of times as there are rows in sheet 2.
To illustrate. Below are the values in E and F only (example 4 rows)
Suzy John
Harry Max
Shevy Twinkle
Max Rover
The above is what came to sheet 2 via a copy.
I want the result to be:
Suzy John
Suzy John
Suzy John
Suzy John
Each time the Macro runs will have a different number of rows to copy down. And of course if there is only one row (which is possible!!) there is not need to copy anything as the correct information is in that row.
I hope this is a better explanation.
Thank you for your time!!
Jeanine
It is always so hard to describe things. What was clear in my mind - is obviously not clear to another!
If I can get this to work - this macro will actually be in the middle of a much larger macro that actually works (except this one important step)!
Sheet 1 has data. What shows up on Sheet 1 is a result of some look ups. Not part of a Macro at all. It is dog information. Name and Owner (and a few other bits). So the user keys in a number and up pops all the information from the database about that dog. How many lines pop up could be 1 or any number up to about 250.
The Macro I have copies these lines to another sheet. Sheet 2. Sheet 2 will have columns A to G. And as I said - an undetermined number of lines. As many lines as there are records for the dog in the database.
Sheet 2 only has the lines copied from Sheet 1. Meaning there are no lines with spaces or blanks. Only the results from page 1 are on page 2. They get copied in by my Macro.
So now we are on sheet 2. Lets for my example say we copied in 11 rows.
I need the value in E1 and F1 to be copied down 10 times (11-1). So that all 11 rows have the same value in Column E and F.
The values in the other columns can not be change.
How many times do we have to copy? The same number of times as there are rows in sheet 2.
To illustrate. Below are the values in E and F only (example 4 rows)
Suzy John
Harry Max
Shevy Twinkle
Max Rover
The above is what came to sheet 2 via a copy.
I want the result to be:
Suzy John
Suzy John
Suzy John
Suzy John
Each time the Macro runs will have a different number of rows to copy down. And of course if there is only one row (which is possible!!) there is not need to copy anything as the correct information is in that row.
I hope this is a better explanation.
Thank you for your time!!
Jeanine
TrowaD
Feb 21, 2013 at 11:10 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Feb 21, 2013 at 11:10 AM
Hi Jeanine,
Things got a lot clearer!
Good to tell this is part of a code.
I don't know on which sheet you are when this part of the code is activated, so I started out with referring to the correct sheet.
Second, when only 1 row exists nothing should be done. Normally I would exit sub, but now the code will skip the autofill part and continue with the code.
So just make sure the variable lRow isn't used before and/or is ok to be changed and this code is safe to implement as part of your code (of course without the Sub and End Sub parts).
Good luck with your code and let me know if more assistance is desired.
Best regards,
Trowa
Things got a lot clearer!
Good to tell this is part of a code.
I don't know on which sheet you are when this part of the code is activated, so I started out with referring to the correct sheet.
Second, when only 1 row exists nothing should be done. Normally I would exit sub, but now the code will skip the autofill part and continue with the code.
So just make sure the variable lRow isn't used before and/or is ok to be changed and this code is safe to implement as part of your code (of course without the Sub and End Sub parts).
Sub CopyValueDown() Dim lRow As Integer Sheets("Sheet 2").Select lRow = Range("A" & Rows.Count).End(xlUp).Row If lRow = 1 Then GoTo NextPartOfCode Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow) NextPartOfCode: End Sub
Good luck with your code and let me know if more assistance is desired.
Best regards,
Trowa
JW32
Feb 21, 2013 at 03:56 PM
- Posts
- 8
- Registration date
- Friday January 4, 2013
- Status
- Member
- Last seen
- February 21, 2013
Feb 21, 2013 at 03:56 PM
Trowa!!
This is great! It is almost perfect. Actually it was perfect until I just ran for the 14th time.
As it turns out there are some records where the last character in E1 has a #.
Such as:
Suzy3 John
When I use your code - if it says only Suzy John it works perfectly.
If it Says Suzy3 John it increments the last digit.
So I ended up with:
Suzy3 John
Suzy4 John
Suzy5 John
Suzy6 John
When I needed:
Suzy3 John
Suzy3 John
Suzy3 John
Suzy3 John
I am so sorry I didn't realize this might happen. The data has about 12K lines so I didn't notice that some records end with a number.
Is there a way to adjust for this oddity?
Thank you!
Jeanine
This is great! It is almost perfect. Actually it was perfect until I just ran for the 14th time.
As it turns out there are some records where the last character in E1 has a #.
Such as:
Suzy3 John
When I use your code - if it says only Suzy John it works perfectly.
If it Says Suzy3 John it increments the last digit.
So I ended up with:
Suzy3 John
Suzy4 John
Suzy5 John
Suzy6 John
When I needed:
Suzy3 John
Suzy3 John
Suzy3 John
Suzy3 John
I am so sorry I didn't realize this might happen. The data has about 12K lines so I didn't notice that some records end with a number.
Is there a way to adjust for this oddity?
Thank you!
Jeanine
rizvisa1
Feb 23, 2013 at 07:50 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Feb 23, 2013 at 07:50 AM
see if changing this line
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow)
to
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow), Type:=xlFillSeries
helps
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow)
to
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow), Type:=xlFillSeries
helps
TrowaD
Feb 25, 2013 at 10:32 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Feb 25, 2013 at 10:32 AM
Hi Jeanine,
Change the line:
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow)
into:
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow), Type:=xlLinearTrend
This should prevent the numbers from going up.
Oddity solved, right?
Best regards,
Trowa
Change the line:
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow)
into:
Range("E1:F1").AutoFill Destination:=Range("E1:F" & lRow), Type:=xlLinearTrend
This should prevent the numbers from going up.
Oddity solved, right?
Best regards,
Trowa
Krebs
>
TrowaD
Mar 8, 2017 at 10:54 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Mar 8, 2017 at 10:54 AM
Great!!!
It helped me a lot!
It helped me a lot!
TrowaD
Sep 27, 2016 at 11:30 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Sep 27, 2016 at 11:30 AM
Hi Diddy,
The code below will take the x amount of rows to be copied from Sheet2 cell A1.
Then takes the value from Sheet1 cell A1 and copies it down the amount found in Sheet2 cell A1.
Here is the code:
Best regards,
Trowa
The code below will take the x amount of rows to be copied from Sheet2 cell A1.
Then takes the value from Sheet1 cell A1 and copies it down the amount found in Sheet2 cell A1.
Here is the code:
Sub RunMe() Dim CopyX, x As Integer CopyX = Sheets("Sheet2").Range("A1") Sheets("Sheet1").Select Range("A1").Copy Do x = x + 1 Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Loop Until x = CopyX Application.CutCopyMode = False End Sub
Best regards,
Trowa
Didn't find the answer you are looking for?
Ask a question
TrowaD
Jan 19, 2017 at 11:13 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jan 19, 2017 at 11:13 AM
Hi Sravan-kr,
"what if i want to do the same in columns please reply me as early as possible"
For that request try this:
Best regards,
Trowa
"what if i want to do the same in columns please reply me as early as possible"
For that request try this:
Sub RunMe() Dim CopyX, x As Integer CopyX = Sheets("Sheet2").Range("A1") Sheets("Sheet1").Select Range("A1").Copy Do x = x + 1 Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial Loop Until x = CopyX Application.CutCopyMode = False End Sub
Best regards,
Trowa
sravan-kr
Jan 20, 2017 at 08:41 AM
- Posts
- 2
- Registration date
- Wednesday January 18, 2017
- Status
- Member
- Last seen
- January 20, 2017
Jan 20, 2017 at 08:41 AM
Thank you soo much for your reply, instead of copying can we run a macro ??
TrowaD
Jan 23, 2017 at 11:59 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jan 23, 2017 at 11:59 AM
"instead of copying can we run a macro "
What do you mean by this? By using the code you are running a macro.
What else do you want to do instead of copying? You want to cut instead of copy?
A confusing question.
Best regards,
Trowa
What do you mean by this? By using the code you are running a macro.
What else do you want to do instead of copying? You want to cut instead of copy?
A confusing question.
Best regards,
Trowa
SriniPNV
Jan 26, 2017 at 11:25 AM
- Posts
- 1
- Registration date
- Thursday January 26, 2017
- Status
- Member
- Last seen
- January 26, 2017
Jan 26, 2017 at 11:25 AM
I am facing Similar Problem , need your help
Input
S.No Item Name Unique Number Number of Times
1 Item 1 123 456 ABCD 6
2 Item 2 124 456 ABFF 1
3 Item 3 125 456 ABEE 3
Output
S.No Item Name Unique Number Number of Times
1 Item 1 123 456 ABCD
123 456 ABCD
123 456 ABCD
123 456 ABCD
123 456 ABCD
123 456 ABCD 6
2 Item 2 124 456 ABFF 1
3 Item 3 125 456 ABEE
125 456 ABEE
125 456 ABEE 3
I have input.xlx file , the sheet 1 contains input ,
Need to automate creating Output.xlx file. Please help me
Input
S.No Item Name Unique Number Number of Times
1 Item 1 123 456 ABCD 6
2 Item 2 124 456 ABFF 1
3 Item 3 125 456 ABEE 3
Output
S.No Item Name Unique Number Number of Times
1 Item 1 123 456 ABCD
123 456 ABCD
123 456 ABCD
123 456 ABCD
123 456 ABCD
123 456 ABCD 6
2 Item 2 124 456 ABFF 1
3 Item 3 125 456 ABEE
125 456 ABEE
125 456 ABEE 3
I have input.xlx file , the sheet 1 contains input ,
Need to automate creating Output.xlx file. Please help me
TrowaD
Jan 30, 2017 at 11:28 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jan 30, 2017 at 11:28 AM
Hi SriniPNV,
The following code will keep the format of your input data.
Here it is:
Best regards,
Trowa
The following code will keep the format of your input data.
Here it is:
Sub RunMe() Dim x, y, z As Integer x = Range("D" & Rows.Count).End(xlUp).Row For y = x To 2 Step -1 z = Cells(y, "D").Value - 1 Do Until z = 0 Rows(y).Copy Rows(y).Insert Shift:=xlDown z = z - 1 Loop Next y Application.CutCopyMode = False End Sub
Best regards,
Trowa
jusip16
Mar 9, 2017 at 12:34 AM
- Posts
- 3
- Registration date
- Monday March 6, 2017
- Status
- Member
- Last seen
- March 9, 2017
Mar 9, 2017 at 12:34 AM
Hi Trowa
Could you help me with a bit similar problem.
I have a sales report provided to me monthly that has the following details: product code, product name, year and month. The format looks like this
l2017 l2017l 2017 l
Code l Name l JAN l FEB l MAR l
I would like to transpose the data to this format using VBA or formulas so that I can do this automatically every month.
Code l Name l Year l Month l Amount l
Also, new product codes and product names are added every month in the report.
Thanks
Could you help me with a bit similar problem.
I have a sales report provided to me monthly that has the following details: product code, product name, year and month. The format looks like this
l2017 l2017l 2017 l
Code l Name l JAN l FEB l MAR l
I would like to transpose the data to this format using VBA or formulas so that I can do this automatically every month.
Code l Name l Year l Month l Amount l
Also, new product codes and product names are added every month in the report.
Thanks
TrowaD
Mar 9, 2017 at 10:46 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Mar 9, 2017 at 10:46 AM
Hi Jusip16,
Could you provide a bit more sample data?, as it is unclear to me what needs to go where.
Best regards,
Trowa
Could you provide a bit more sample data?, as it is unclear to me what needs to go where.
Best regards,
Trowa
jusip16
Mar 9, 2017 at 10:41 PM
- Posts
- 3
- Registration date
- Monday March 6, 2017
- Status
- Member
- Last seen
- March 9, 2017
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Mar 9, 2017 at 10:41 PM
Hi Trowa,
Sure. Here is a sample data for 2015
l Code l Name l JAN l FEB l MAR l
l 145 l Socks l 20 l 74 l 52 l
l 992 l Bags l 52 l 102 l 278 l
I would like to change it to something like this
l Code l Name l Month l Amount
l 145 l Socks l JAN l 20
l 145 l Socks l FEB l 74
l 145 l Socks l MAR l 52
l 992 l Bags l JAN l 52
l 992 l Bags l FEB l 102
l 992 l Bags l MAR l 278
Thanks again
Sure. Here is a sample data for 2015
l Code l Name l JAN l FEB l MAR l
l 145 l Socks l 20 l 74 l 52 l
l 992 l Bags l 52 l 102 l 278 l
I would like to change it to something like this
l Code l Name l Month l Amount
l 145 l Socks l JAN l 20
l 145 l Socks l FEB l 74
l 145 l Socks l MAR l 52
l 992 l Bags l JAN l 52
l 992 l Bags l FEB l 102
l 992 l Bags l MAR l 278
Thanks again
Robert U.
>
jusip16
Jul 17, 2017 at 06:28 AM
- Posts
- 3
- Registration date
- Monday March 6, 2017
- Status
- Member
- Last seen
- March 9, 2017
Jul 17, 2017 at 06:28 AM
Any news on this issue? I'd use it as well.
TrowaD
Jul 18, 2017 at 11:19 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jul 18, 2017 at 11:19 AM
Hi Robert,
Sure, must have slipped my attention.
Assuming a second sheet is created with header.
In the code the source sheet is called: Sheet1.
And the destination sheet is called: Sheet2.
Here is the code:
Best regards,
Trowa
Sure, must have slipped my attention.
Assuming a second sheet is created with header.
In the code the source sheet is called: Sheet1.
And the destination sheet is called: Sheet2.
Here is the code:
Sub RunMe() Dim mCol, mRow As Integer Sheets("Sheet1").Select For Each cell In Range("A2:A" & Range("A1").End(xlDown).Row) mRow = cell.Row mCol = 2 Do mCol = mCol + 1 If Cells(mRow, mCol) <> vbNullString Then Range(Cells(mRow, "A"), Cells(mRow, "B")).Copy _ Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) Cells(1, mCol).Copy Sheets("Sheet2").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) Cells(mRow, mCol).Copy Sheets("Sheet2").Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) End If Loop Until Cells(mRow, mCol) = vbNullString Next cell End Sub
Best regards,
Trowa
TanyaBorzova
Jul 23, 2017 at 08:56 PM
- Posts
- 4
- Registration date
- Sunday July 23, 2017
- Status
- Member
- Last seen
- July 28, 2017
Jul 23, 2017 at 08:56 PM
Hi Trowa, I see you've helped some people already. I have a very similar task and hope you'll be able to help me as well.
I have a lot of unique rows in one column on tab "Pivot". For simplicity, let's take only three (A1:A3):
aaa
bbb
ccc
I need these values to repeat 14 times each one after the other on another tab "Results".
What code do I use? I used your initial code and modified it a bit:
Sub ToyNumber()
Dim toy, i As Integer
toy = 1
For Each cell In Range("A1", Range("A1").End(xlDown))
For y = 1 To 14
Range("B" & toy).Value = cell.Value
toy = toy + 1
Next i
Next cell
End Sub
I'm struggling how to get the output on a different tab (sheet). I'd appreciate your soonest assistance. Thank you.
I have a lot of unique rows in one column on tab "Pivot". For simplicity, let's take only three (A1:A3):
aaa
bbb
ccc
I need these values to repeat 14 times each one after the other on another tab "Results".
What code do I use? I used your initial code and modified it a bit:
Sub ToyNumber()
Dim toy, i As Integer
toy = 1
For Each cell In Range("A1", Range("A1").End(xlDown))
For y = 1 To 14
Range("B" & toy).Value = cell.Value
toy = toy + 1
Next i
Next cell
End Sub
I'm struggling how to get the output on a different tab (sheet). I'd appreciate your soonest assistance. Thank you.
TanyaBorzova
Jul 24, 2017 at 12:07 AM
- Posts
- 4
- Registration date
- Sunday July 23, 2017
- Status
- Member
- Last seen
- July 28, 2017
- Posts
- 4
- Registration date
- Sunday July 23, 2017
- Status
- Member
- Last seen
- July 28, 2017
Jul 24, 2017 at 12:07 AM
One correction: should be i instead of y
Sub ToyNumber()
Dim toy, i As Integer
toy = 1
For Each cell In Range("A1", Range("A1").End(xlDown))
For i = 1 To 14
Range("B" & toy).Value = cell.Value
toy = toy + 1
Next i
Next cell
End Sub
Sub ToyNumber()
Dim toy, i As Integer
toy = 1
For Each cell In Range("A1", Range("A1").End(xlDown))
For i = 1 To 14
Range("B" & toy).Value = cell.Value
toy = toy + 1
Next i
Next cell
End Sub
TanyaBorzova
Jul 24, 2017 at 01:33 AM
- Posts
- 4
- Registration date
- Sunday July 23, 2017
- Status
- Member
- Last seen
- July 28, 2017
Jul 24, 2017 at 01:33 AM
In addition, I need values on sheet Results to start from cell A3, not A1, so that the first aaa will be in cell A3. I'm not sure how to modify this part: Range("B" & toy).Value = cell.Value.
TrowaD
Jul 25, 2017 at 11:16 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jul 25, 2017 at 11:16 AM
Hi Tanya,
Here is the amended code:
That should do it, right?
Best regards,
Trowa
Here is the amended code:
Sub ToyNumber() Dim toy, i As Integer toy = 3 'changed 1 to 3, to start pasting from row 3. For Each cell In Range("A1", Range("A1").End(xlDown)) For i = 1 To 14 Sheets("Result").Range("A" & toy).Value = cell.Value 'reference to the Result sheet added, also changed B to A. toy = toy + 1 Next i Next cell End Sub
That should do it, right?
Best regards,
Trowa
TanyaBorzova
Jul 28, 2017 at 11:37 AM
- Posts
- 4
- Registration date
- Sunday July 23, 2017
- Status
- Member
- Last seen
- July 28, 2017
Jul 28, 2017 at 11:37 AM
Thank you Trowa!!! It works perfectly. I have been struggling with this marco for the last two weeks! You've made my life much easier. Thanks.
annonyman
Sep 5, 2017 at 04:26 AM
- Posts
- 2
- Registration date
- Tuesday September 5, 2017
- Status
- Member
- Last seen
- September 18, 2017
Sep 5, 2017 at 04:26 AM
Hi TrowaD!
I am struggling with something similar to what has been described above.
I want to copy part of row (say A9:M9) and paste it down below (A10:M10, A11:M11 etc.) for the number of times specified by a cell value (e.g. the value of B4), which contains a row count function. Cells A9:M9 contain functions.
Would be greatful if you could help me, thanks.
I am struggling with something similar to what has been described above.
I want to copy part of row (say A9:M9) and paste it down below (A10:M10, A11:M11 etc.) for the number of times specified by a cell value (e.g. the value of B4), which contains a row count function. Cells A9:M9 contain functions.
Would be greatful if you could help me, thanks.
TrowaD
Sep 12, 2017 at 10:39 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Sep 12, 2017 at 10:39 AM
Hi Annonyman,
Give the following code a try:
Best regards,
Trowa
Give the following code a try:
Sub RunMe() Dim x As Integer x = Range("B4").Value Range("A9:M9").AutoFill Destination:=Range("A9:M" & 9 + x) End Sub
Best regards,
Trowa
annonyman
Sep 18, 2017 at 09:34 AM
- Posts
- 2
- Registration date
- Tuesday September 5, 2017
- Status
- Member
- Last seen
- September 18, 2017
Sep 18, 2017 at 09:34 AM
Works perfectly, thanks!
syapinme
Sep 25, 2017 at 09:40 AM
- Posts
- 1
- Registration date
- Monday September 25, 2017
- Status
- Member
- Last seen
- September 25, 2017
Sep 25, 2017 at 09:40 AM
Hello! My issue is a combination of some others. I'm trying to have multiple columns copy/paste based on a number entered.
So, a user would enter number X into A2. I would then want to copy/paste columns E:H next to each other X number of times. This seems to be a combo of what annonyman and Sravan-kr asked, but I keep getting thrown in the Do statement.
Any help would be much appreciated. Thank you!
So, a user would enter number X into A2. I would then want to copy/paste columns E:H next to each other X number of times. This seems to be a combo of what annonyman and Sravan-kr asked, but I keep getting thrown in the Do statement.
Any help would be much appreciated. Thank you!
TrowaD
Sep 28, 2017 at 11:09 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Sep 28, 2017 at 11:09 AM
Hi Syapinme,
Give the following code a try:
Best regards,
Trowa
Give the following code a try:
Sub RunMe() Dim x As Integer x = Range("A2").Value Do Columns("E:H").Copy Columns("E").Insert x = x - 1 Loop Until x = 0 Application.CutCopyMode = False End Sub
Best regards,
Trowa
TrowaD
Nov 6, 2017 at 10:49 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Nov 6, 2017 at 10:49 AM
Hi mfinley,
Is the entered name located in a cell or do you want Excel to ask you?
Is the x/y number of rows entered in a cell or do you want Excel to ask you?
Where do you want the names to be pasted?
Best regards,
Trowa
Is the entered name located in a cell or do you want Excel to ask you?
Is the x/y number of rows entered in a cell or do you want Excel to ask you?
Where do you want the names to be pasted?
Best regards,
Trowa
Hi Trowa,
great to see your coding skills. I'm completely new to Macro.
I have a sheet in the format as below, where a,b,c,in first row is for depicting column :
a b c d e f
cc vs vd af sa 4
as dr gy hn jm 1
qw dr gy jh lk 3
and I want this table in the format of :
a b c d e f
cc vs vd af sa 1
cc vs vd af sa 1
cc vs vd af sa 1
cc vs vd af sa 1
as dr gy hn jm 1
qw dr gy jh lk 1
qw dr gy jh lk 1
qw dr gy jh lk 1
please help. with the Macro
great to see your coding skills. I'm completely new to Macro.
I have a sheet in the format as below, where a,b,c,in first row is for depicting column :
a b c d e f
cc vs vd af sa 4
as dr gy hn jm 1
qw dr gy jh lk 3
and I want this table in the format of :
a b c d e f
cc vs vd af sa 1
cc vs vd af sa 1
cc vs vd af sa 1
cc vs vd af sa 1
as dr gy hn jm 1
qw dr gy jh lk 1
qw dr gy jh lk 1
qw dr gy jh lk 1
please help. with the Macro
TrowaD
Jan 30, 2018 at 11:35 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Jan 30, 2018 at 11:35 AM
Hi Rahul,
Not sure if you are still stuck on this, but here is what I got for you:
Best regards,
Trowa
Not sure if you are still stuck on this, but here is what I got for you:
Sub RunMe() Dim x, xRep, lRow As Integer lRow = Range("F" & Rows.Count).End(xlUp).Row For x = lRow To 1 Step -1 xRep = Range("F" & x).Value Do Until xRep = 1 With Rows(x) .Copy .Insert End With xRep = xRep - 1 Loop Next x Application.CutCopyMode = False End Sub
Best regards,
Trowa
Mar 21, 2017 at 11:35 AM
I am searching for a code that will copy a number 80 times down a column, then move on to the next number and copy that number down 80x.
My inputs are a list (it will change every week) of numbers:
1
3
4
78
98
108
I wish to put these numbers in a column beside it,but repeating the numbers 80 times.
Output:
1
1
1
1
(76 more 1's down)
3
3
3
3
(76 more 3's down)
thank you for any help!
Mar 21, 2017 at 12:15 PM
Assuming your data is located in column A, then the following code will place the result in column B:
Best regards,
Trowa
Mar 21, 2017 at 01:07 PM
Aug 25, 2017 at 07:58 AM
I have try this code
Sub RunMe()
Dim x, y As Integer
x = 1
For Each cell In Range("A1", Range("A1").End(xlDown))
For y = 1 To 80
Range("B" & x).Value = cell.Value
x = x + 1
Next y
Next cell
End Sub
but this is not working if we have only 1 cell used in column, if we have used only one cell in column . ablove code not working fine I=, its got hang the sheet
Jul 6, 2019 at 06:35 AM
I have a similar kind of excel sheet which I want to convert and format to a particular usage. Please let me know where I can share the excel sheet.
Thanks in advance
Kunal