how to fix this Macros

[nathan663]

I have the following Macro:

ActiveWindow.SmallScroll Down:=21
Rows("39:39").Select
Selection.Copy
Sheets("Summary").Select
Rows("4:4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B18").Select
Columns("B:B").EntireColumn.AutoFit
Range("D13").Select
Sheets("Individual - Non-Approved").Select
Range("A31:K31").Select

How can i amend this so when it pastes into the "summary" sheet it will look for the next available row, and not just row 4. as stated in the macro?

im guessing its a simple fix?

 

[Domski]

Try:

Dim lastrow As Long
lastrow = Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row + 1
Rows("39:39").Copy
Sheets("Summary").Range("A" & lastrow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("B:B").EntireColumn.AutoFit
Sheets("Individual - Non-Approved").Range("A31:K31").Select

Dom

 

[Jubjab]

Something like this:

Sub a()

Rows(39).Copy
freerow = Sheets("Summary").Rows(4).End(xlDown).Row + 1
Cells(freerow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("B:B").EntireColumn.AutoFit
Sheets("Individual - Non-Approved").Select
Range("A31:K31").Select

End Sub

 

[nathan663]

it did not like the

Cells(freerow, 1).Select

Section, any ideas?

 

[Peter_SSs]

Nathan

In code, you generally do not have to activate/select things to work with them. In additions activation/selection is a relatively slow process so try to pick up tips about working without these actions.

Try this on a copy of your workbook.

Instead of ActiveSheet, you may want
Sheets("Individual - Non-Approved")
but I'm not certain about which sheet you are copying from.

<font face=Courier New>ActiveSheet.Rows("39").Copy<br><SPAN style="color:#00007F">With</SPAN> Sheets("Summary")<br>    .Range("A" & Rows.Count).End(xlUp).Offset(1) _<br>        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _<br>            SkipBlanks:=False, Transpose:=<SPAN style="color:#00007F">False</SPAN><br>    .Columns("B").AutoFit<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>Application.CutCopyMode = False</FONT>

 

[nathan663]

Nathan

In code, you generally do not have to activate/select things to work with them. In additions activation/selection is a relatively slow process so try to pick up tips about working without these actions.

Try this on a copy of your workbook.

Instead of ActiveSheet, you may want
Sheets("Individual - Non-Approved")
but I'm not certain about which sheet you are copying from.

ActiveSheet.Rows("39").Copy
With Sheets("Summary")
.Range("A" & Rows.Count).End(xlUp).Offset(1) _
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.Columns("B").AutoFit
End With
Application.CutCopyMode = False

This was a recorded macro. I have only started working with macros in the last week or so. I am copying from Individual - Non Approved to the Summary Sheet

 

[Peter_SSs]

This was a recorded macro. I have only started working with macros in the last week or so.

The macro recorder is a good place to start, but keep looking out for ways to improve that code because it mostly does not produce very efficient code.

I am copying from Individual - Non Approved to the Summary Sheet

Then I would change the first line of my suggested code to:

Sheets("Individual - Non-Approved").Rows("39").Copy