Help Fixing Copy/Paste VBA

**juniperjacobs** · 11-07-2011, 10:54 AM

I have this code that I need to tweak a few things on.
1. I need it to work regardless of the tab name, having some troubles when I take out the worksheet name.
2. The goal is to copy rows 7:37 and paste in the next available row the selected number of times, but it's not doing it.

here's the code:

Sub Copy_7_to_37_Paste_in_First_Blank_Row()

Dim NextRow As Long

With Worksheets("Dept.Store")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

rDstNr = InputBox("How many times shall I paste it?", "Row Paster...", 1)
Worksheets("1").Range("a7:ac37").Copy _
Destination:=Worksheets("1").Cells(NextRow, "a")

End Sub

**JBeaucaire** · 11-07-2011, 11:24 AM

Try this:

Sub Copy_7_to_37_Paste_in_First_Blank_Row()
Dim NextRow As Long, rDstNr As Long

rDstNr = Application.InputBox("How many times shall I paste it?", _
                              "Row Paster...", 1, Type:=1)
If rDstNr < 1 Then Exit Sub

With Sheets("Dept.Store")
    NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    Sheets(1).Range("A7:A37").EntireRow.Copy .Range("A" & NextRow).Resize(rDstNr * 31)
End With

End Sub

Special note: Sheets("1") and Sheets(1) are completely different things.
Sheets(1) - the first indexed sheet in the workbook, could have any name
Sheets("1") - a worksheet named "1" found anywhere in the workbook

**juniperjacobs** · 11-07-2011, 12:04 PM

JBeaucaire:
The VBA you provided works but only if the tab name matches in the coding. saw your note about "" v. no "", but when i tried to name the code active.sheet or workbook it gave me an error. i want to be able to use the code in whatever active tab i'm in regardless of the name.

I tried this, but it doesn't seem to work:

Sub Copy_7_to_37_Paste_in_First_Blank_Row()
Dim NextRow As Long, rDstNr As Long

rDstNr = Application.InputBox("How many times shall I paste it?", _
                              "Row Paster...", 1, Type:=1)
If rDstNr < 1 Then Exit Sub

With Active.Sheet
    NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    Active.Sheet.Range("A7:A37").EntireRow.Copy .Range("A" & NextRow).Resize(rDstNr * 31)
End With

End Sub

**snb** · 11-07-2011, 11:34 AM

Sub Copy_7_to_37_Paste_in_First_Blank_Row_snb()
 With ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).EntireRow.Resize(30)
  .Value = activesheet.Rows(7).Resize(30).Value
  .AutoFill .Resize(30 * (InputBox("how many times") - 1)), xlFillCopy
 End With
End Sub

**JBeaucaire** · 11-07-2011, 12:24 PM

There is no Active.Sheet reference, it is Activesheet.

So, this macro is intended to run solely on the activesheet?

Sub Copy_7_to_37_Paste_in_First_Blank_Row()
Dim NextRow As Long, rDstNr As Long

rDstNr = Application.InputBox("How many times shall I paste it?", _
                              "Row Paster...", 1, Type:=1)
If rDstNr < 1 Then Exit Sub

With Activesheet
    NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    .Range("A7:A37").EntireRow.Copy .Range("A" & NextRow).Resize(rDstNr * 31)
End With

End Sub