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.

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this return the address of the range you want?

MsgBox Range("A1:J" & Range("A" & Rows.Count).End(xlUp).Row)

 

[maghabo]

Hi Andrew thanks for the reply, here is my vba:
------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub CopyValue()
Range("A1:k200").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("m1:m200").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
---------------------------------------------------------------------------------------------------------------------------------------------------------

Currently the copy and paste are predefined to a1:k200 and m1:m200
i would like it to be from a1:the last row with data. Please confirm, thanks.

 

[Andrew Poulsom]

Try:

Sub CopyValue()
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    With Range("A1:K" & LastRow)
        .Value = .Value
    End With
    With Range("M1:M" & LastRow)
        .Value = .Value
    End With
End Sub

 

[maghabo]

Hi Andrew that works like a charm, spot on. I have another questions which is not related to the previous thread.
I have vba on the whole workbook as follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


If SaveAsUI = False Then


Cancel = True


MsgBox "Gunakan tombol Save untuk save order"

If SaveAsUI = True Then
Cancel = True


End If
End If


End Sub
---------------------------------------------------------------------------------------------------------------------------------------------------------------------


this will not allow the user to save or ctrl+s, i did this to prevent users to overwrite my template. Then i have a save vba as follows:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sub save()
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


''Worksheets.Select
'' Cells.Select
'' Selection.Copy
'' Selection.PasteSpecial Paste:=xlPasteValues
'' ActiveSheet.Select
'' Application.CutCopyMode = False


CopyValue


path = "\\192.168.1.2\g\database\orders\LR\"
filename1 = Range("C5")
filename2 = Range("C3")
filename3 = Range("i3")
ActiveWorkbook.saveas Filename:=path & filename1 & "-" & filename2 & "-" & filename3 & ".xls", FileFormat:=xlNormal
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------------------------

however this is not working because i have the first vba on the workbook, if i delete the first vba, the save vba above will work. Is there any workaround to allow the save vba to still work with the first vba (which not allow the user to save/ ctrl+s) still there. Your help will be greatly appreciated. Thanks so much andrew

 

[Andrew Poulsom]

Try this:

Application.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=path & filename1 & "-" & filename2 & "-" & filename3 & ".xls", FileFormat:=xlNormal
Application.EnableEvents = True

 

[maghabo]

Hi Andrew,

that works perfectly. I have two other questions for you to ask.

1. using vba
Sub protect()


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


End Sub

how do you make it dynamically (only to protect the first cell up to the last cell with data, for example the first cell is predefined A6 and the last cell should be the last cell with data, lets say that we are working up to column J).

2. how to define that vba should be applicable to every worksheets? where do you place them. For example i have this vba:
-------------------------------------------------------------------------------------------------------------------------------------------------------
Sub save()
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


''Worksheets.Select
'' Cells.Select
'' Selection.Copy
'' Selection.PasteSpecial Paste:=xlPasteValues
'' ActiveSheet.Select
'' Application.CutCopyMode = False


CopyValue


path = "\\192.168.1.2\g\database\orders\LR\"
filename1 = Range("C5")
filename2 = Range("C3")
filename3 = Range("i3")
ActiveWorkbook.saveas Filename:=path & filename1 & "-" & filename2 & "-" & filename3 & ".xls", FileFormat:=xlNormal
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------------
I appreciate your help in advance andrew. Thanks!

 

[Andrew Poulsom]

For your first question try:

Sub ProtectSheet()
    Dim LastRow As Long
    With ActiveSheet
        .Cells.Locked = False
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("A6:J" & LastRow).Locked = True
        .Protect Password:="maghabo", AllowFiltering:=True
    End With
End Sub

For your second question:

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws.Cells
            .Value = .Value
        End With
    Next ws

 

[maghabo]

HI Andrew,

One last question: how to add an exception for question no. 2, i would like the vba to not run on lets say sheet(2). Please confirm thanks andrew

 

[Andrew Poulsom]

Example:

    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