Hi,
I am struggling to create a macro that can copy an excel range of data (A1:B50) from sheet 1 and paste into sheet 2 for 4000 times. Currently, sheet 2 contains a column of data with 4000 rows. Hence, i am trying to insert and paste (A1:B50) from sheet 1 into each row in sheet 2. it should visually look like this:
Sheet 1(A1:B50)
1 Cat
2 Dog
.
.
.
49 Bird
Sheet 2
Row# Name
1 Mr Tom
2 Mr Pat
.
.
.
4000 Ms Mary
The final version should be
Sheet 2
# Name
Row1 Mr Tom
1 Cat
2 Dog
.
.
.
49 Bird
If you are, then RECORD A MACRO WITH A CUT AND PASTE OF THE DATA YOU WANT, AND LOOP IT. Then go into the macro and edit it to do what you want!
Make certian to initialize a variable for the CURRENTROW, as you will need to paste starting at a different point each time. Start with that, and we build from there!!!!
This is the easiest way to learn, because we can post some code, that you will fail to deploy, because you dont quite understand what the code does. This way! You will watch it grow, as we assist you with the changes!
This is how the code looks when i combine yours with the recorded macro. I keep getting an error 1004 saying select method of range failed. Is something missing in the code? thanks.
Sub Copy_3()
Dim thecount
Dim theendcount
theendcount = 10
For thecount = 0 To theendcount
Worksheets("Sheet2").Range("A2:C48").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Next
End Sub
Btw, once this loop has completed once it will just diplicate it ten more times. You have keep track of the count and advance the rows by a multiple of the count
Sub RunMe()
Dim mCount, mRow As Integer
mRow = 2
Do
Sheets("Sheet1").Range("A1:B5").Copy
Sheets("Sheet2").Range(Cells(mRow, "A"), Cells(mRow, "B")).Insert shift:=xlDown
mRow = mRow + 6
mCount = mCount + 1
Loop Until mCount = 4
End Sub
Will change the data on Sheet2 into:
1 Tom
1 Cat
2 Dog
3 Mouse
4 Lion
My result looks like this in sheet 2:
1 Cat
2 Dog
3 Mouse
4 Lion
.
.
.
40 Lion
pasted 10 times(1-40) Then the next row had the following below
41 Tom
42 Pat
43 Mike
44 Hank
Cat, Dog, Mouse and Lion were not pasted in between Tom, Pat, Mike and Hank. The actual data that i copied had 3 columns and 47 rows that is why i made mRow= mRow + 47. Was i wrong to do this?
This is the best forum regarding Ms. Excel for both beginners as well as the experienced personals. If they follow this forum properly and regularly then they will be learning a lot regarding the software. Most of the logo design companies also maintain their data over Excel as it helps them in sorting and finding the relevant data quickly.
'Plus cutcopymode is either xlcopy or xlcut, not true or false. '
Application.CutCopyMode = False is used to clear the clipboard.
'You have to specify the activeworkbook before selecting the sheet. '
This is not necessary when using the code within the same workbook.
Dim thecount
Dim theendcount
Theendcount=10
For thecount= 0 to theendcount
Thisworkbook.worksheets ("sheet2").range (yourrange).copy thisworkbook.worksheets ("sheet3").range (yourrange)
Next