[Solved] VBA : Copying information to the next blank row

spnz · Apr 23rd 2004

Hi there....

I am working on a code that copies information to another sheet and then places the information in the next blank cell.

I have a code that im trying to use (Taken from a userform) that doesn't work how I would like it. It doesn't move to the next blank cell and copy.

Sub Transfer2direct()

Application.ScreenUpdating = False
Dim free_row As Integer
' This counts the number of rows in use, and adds 1 to find the next empty one
free_row = Worksheets("Direct Report").UsedRange.Rows.Count + 1
' "With" statement to save on the amount of code
With Worksheets("Direct Report")

On Error Resume Next
Sheet2.Visible = xlSheetVisible
Sheet20.Visible = xlSheetVisible
'Unhides both sheets
On Error Resume Next
With Sheets("timesheet").Select
Selection.AutoFilter Field:=5, Criteria1:="Direct (Desp) / 817"
End With
On Error GoTo 0
With Columns("F:H").Select
Selection.EntireColumn.Hidden = False
Selection.AutoFilter Field:=6, Criteria1:="EARLY"
Sheet12.Visible = xlSheetVisible
Range("timesheet2").Select
Selection.Copy
Sheets("Direct Report").Select
Rows("2:40").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End With

On Error Resume Next
With Sheets("timesheet").Select
Selection.AutoFilter Field:=5, Criteria1:="Direct (Desp) / 817"
End With
On Error GoTo 0
With Columns("F:H").Select
Selection.EntireColumn.Hidden = False
Selection.AutoFilter Field:=6, Criteria1:="LATE"
Sheet12.Visible = xlSheetVisible
Range("timesheet2").Select
Selection.Copy
Sheets("Direct Report").Select
End With

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("a2:a39").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = False

End With
End Sub

Can anyone suggest what I can do to get this working?

Thanks

yjoshi · Apr 23rd 2004

Hi,

I am putting a SIMPLE code to transfer data from Sheet1 to the next blank row in Sheet2.
You can add other flavours like screen updation, selection after paste etc etc.

Just open a blank workbook, paste this code in sheet1 and run it a few times by entering data in sheet1 and selecting the same.

Code

Sub transfer()
Selection.Copy
Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I have taken 4-5 steps in one go.
1. selecting sheet2
2. going to the last cell in A col (A65536)
3. then going up to find last used cell in A col
4. go to next cell
5. paste.

Easy ?? :cool:

spnz · Apr 23rd 2004

Thank-you yjoshi

Worked very well

[Solved] VBA : Copying information to the next blank row

Participate now!

Share