Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

go to another sheet and get lrow on col AC

0

I thought I knew how to do this, but no

I simply want to go to another sheet and get the last row of data in col c

There is about 1200 rows of data in col "AC".  I do go to the other sheet,(DATAEACHSTORE) but not to col "AC"

Thanks  Carroll

Private Sub CommandButton2_Click()
    ' COPY PASTE VALUES NEW DATA FROM DATAEACHSTORE
   ' FIND LAST ROW OF DATA
    Worksheets("DATAEACHSTORE") .Range("AC6")
     lrow = Range("AC6").End(xlDown).Row
     Worksheets("DATAEACHSTORE").Range("E6:T" & lrow).Copy
     MsgBox " LROW = " & lrow
     'Worksheets("COPYPASTEVALUES_FRM THEN SORT-").Range                              (" A6").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Worksheets("COPYPASTEVALUES_FRM THEN SORT->").Range("A6").PasteSpecial Paste:=xlPasteFormats
'MsgBox "DATA COPIED Click to go", , "Example"

End Sub
excelmacros-vba
MS Office Version: 2013
posted by: carroll (rep: 16) on Sun Jan 27, 2019 at 2:09 am
Answer
Discuss

Answers

0
Selected Answer

Please try this code. If the worksheets named in it exist the defined range will be copied and pasted.

Private Sub CommandButton2_Click()
    ' COPY PASTE VALUES NEW DATA FROM DATAEACHSTORE
    ' FIND LAST ROW OF DATA

    Dim Rl As Long
    Dim Rng As Range

    With Worksheets("DATAEACHSTORE")
        Rl = .Cells(.Rows.Count, "AC").End(xlUp).Row
        Set Rng = .Range(.Cells(6, "E"), .Cells(Rl, "T"))
        Rng.Copy
    End With
    MsgBox "Copied range address = " & Rng.Address

    Worksheets("COPYPASTEVALUES_FRM THEN SORT-").Range("A6").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    MsgBox "DATA COPIED Click to go", , "Example"
End Sub
posted by: Variatus (rep: 4889) on Sun Jan 27, 2019 at 5:22 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login