Find last row & column + copy paste

[maghabo16]

Hi i have a questions. I have a sheet that contains data starting from cell A1 to the last row with data. Basically i want to apply copy and paste limited to cell A1 up until the last row with data. Logically we can do this by finding the last row with data and then paste it up to only those last row only, for example lets just say that the data will be ranging from column A-J. Please note that the paste should be paste special value. Any help would be greatly appreciated. Thanks.

 

[maghabo]

Hi Andrew,

Where should i put the codes in this vba?

Dim Err As Boolean


Sub SaveWbWithoutPrompt()
Dim path As String
Dim filename1 As String
Dim filename2 As String
Dim filename3 As String
Err = False
Check_Error_BeforeSave
If Err Then
Exit Sub
End If


unprotect
CopyValue
Sort1
locksheet
protect




path = "C:\Documents and Settings\Lenovo\Desktop\Dropbox\"
filename1 = Range("A1")
filename2 = Range("b4")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & "-" & filename2 & "-" & Format(Date, "yyyy") & ".xls", FileFormat:=xlNormal
End Sub


Sub Check_Error_BeforeSave()

Dim msg As String
Dim Err2 As Boolean
Dim A As Integer

Err2 = False

If Worksheets("IDR").Range("b4").Value = "" Then
msg = msg & vbCrLf & "Periode tidak boleh kosong"
Err = True
End If

If Worksheets("USD").Range("b4").Value = "" Then
msg = msg & vbCrLf & "Periode tidak boleh kosong"
Err = True
End If

For A = 1 To 9999
If Range("A" & A + 5) <> "" And (Range("C" & A + 5)) = "" Then
Err2 = True
End If
Next A
If Err2 = True Then
msg = msg & vbCrLf & "KK/KM masih ada yang kosong"
Err = True
End If

For A = 1 To 9999
If Range("A" & A + 5) <> "" And (Range("d" & A + 5)) = "" Then
Err2 = True
End If
Next A
If Err2 = True Then
msg = msg & vbCrLf & "Jenis transaksi masih ada yang kosong"
Err = True
End If

For A = 1 To 9999
If Range("A" & A + 5) <> "" And (Range("f" & A + 5)) = "" Then
Err2 = True
End If
Next A
If Err2 = True Then
msg = msg & vbCrLf & "Keterangan masih ada yang kosong"
Err = True
End If

For A = 1 To 9999
If Range("C" & A + 5) = "KM" And Val(Range("g" & A + 5)) = 0 Then
Err2 = True
End If
Next A
If Err2 = True Then
msg = msg & vbCrLf & "Debit/ kredit ada yang tidak cocok"
Err = True
End If

For A = 1 To 9999
If Range("C" & A + 5) = "KK" And Val(Range("h" & A + 5)) = 0 Then
Err2 = True
End If
Next A
If Err2 = True Then
msg = msg & vbCrLf & "Debit/ kredit ada yang tidak cocok"
Err = True
End If

If Err Then
MsgBox msg
End If


End Sub


Sub Sort1()


Dim oneRange As Range
Dim aCell As Range


Set oneRange = Range("a5:i9994")
Set aCell = Range("a5")


oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes




End Sub


Sub CopyValue()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
With Range("A6:i" & LastRow)
.Value = .Value
End With
With Range("k6:M" & LastRow)
.Value = .Value
End With
End Sub


Sub locksheet()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A6:i" & LastRow).Locked = True
.protect Password:="maghabo", AllowFiltering:=True
End With
End Sub


Sub unprotect()


ActiveSheet.unprotect Password:="maghabo"


End Sub


Sub protect()


ActiveSheet.protect Password:="maghabo", AllowFiltering:=True


End Sub

 

[Andrew Poulsom]

I don't understand your question, sorry.

When posting code in future can you use Code tags please, so that it is easier to read. Here's how:

BB Code List - MrExcel Message Board

 

[maghabo]

sorry for the confusion andrew, i previously asked on how to apply the vba to all the sheets except on sheet 2. where should i place this on the vba above? you replied
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.name <> "Sheet2" Then
With ws.Cells
.Value = .Value
End With
End If
Next ws

 

[Andrew Poulsom]

I don't know because you don't appear to be looping around worksheets in the code in Post #11.

Did you not see my request to use Code tags when posting VBA code?