VBA Macro to copy entire row to another sheet

[leopard]

Hi All,

I have written Macro to copy every row in "sheet1" 24 times into new sheet called "NewSheet". but it keep giving me error message. Actually, I don't know why. Can any one please help.

here is my macro

Sub CopyRowsBook2()

Worksheets.Add().Name = "NewSheet"
Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

' Loop through each row
For x = 2 To FinalRow
Worksheets("sheet1").Cells(x, 1).Select
ActiveCell.EntireRow.Select
Selection.Copy
' Loop to copy every row 24 times
For i = 1 To 24

Sheets("NewSheet").Select
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste

Next i

Sheets("Sheet1").Select


Next x
End Sub


Thanks in advance,

 

[bjurney]

Code works fine for me, where do you get your error?

 

[leopard]

In second loop it gives me a message saying "Run time error '1004': Application-defined or object- defined error"

 

[bjurney]

When you debug does it highlight a certain line? If so what one exactly?

 

[leopard]

oh, I C

in the second row

Sheets("Sheet1").Select

 

[bjurney]

Did you rename "Sheet1" to something else by chance? If so update your code to the name of the new sheet or try replacing

Sheets("Sheet1").Select

with

Sheet1.Select

 

[leopard]

it still not working even with office 2003 VBA editor
this is making me like crazy

 

[GTO]

As bjurney asked, did you rename "Sheet1"? Check to make sure there are no leading/trailing spaces if you highlight the tab, like "Sheet1 " or " Sheet1".

Or - is Sheet1 hidden?

 

[leopard]

sheet1 has its original name, i have done a little editing and this is my new macro, but it still not working, this is driving me crazy,
now i'm using original sheet1 and sheet 2 as a source and as a destination.

Sub CopyRowsBook2()


Sheets("Sheet1").Select
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

' Loop through each row

Dim x As Integer
Dim i As Integer

For x = 2 To FinalRow
Worksheets("sheet1").Cells(x, 1).Select
ActiveCell.EntireRow.Select
Selection.Copy

For i = 1 To 24

Sheets("sheet2").Select
DestinationRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(DestinationRow, 1).Select
ActiveSheet.Paste

Next i

Sheets("Sheet1").Select


Next x
End Sub

if this work, plz let me know which office u r using 2003 or 2007 and if u plz attach the file that containing working macro.
thanks in advance.

 

[GTO]

It is slow and there are easier ways, but it works fine. I am using excel2000, OS is WIN and Office is XP - on a laptop that is about toast; so lets presume we're safe in those issues.

Put the cursor any place in the sub. Start pressing F8 and step through the code. If you reduce VBIDE's window you can watch everything happen step-by-step. See where (how many inner or outer loops) it gets to when it errors.