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

Cell Range to be expressed with variable

0

Dear Sir/Madam,

While copying and pasting the data from one excel workbook to another, I am facing an error with selection of data range. I have written-- 

        ActiveSheet.Range(Cells(mm, 1), Cells(LastRow1, 31)).Select

        Selection.Cut

        ActiveSheet.Cells(LastRow2 + 1, 1).Select                    

        ActiveSheet.Paste

where mm, LastRow1, LastRow2 are integer variable. This code works perfect when it is used for a single workbook, but it returns error while it is tried for two workbooks. If variables are not used, direct coding like "A2:D40" is written, then it works.

This also works, Workbooks("attendanceGVK.xlsm").Worksheets("DataSheet").Range("A2:AE1000").Cut _

    Workbooks(newfilenem).Worksheets("DataSheet").Range("A5000")

But, my requirement is replace this fixed range with variable

I will be obliged, if you help me inthis regards

Thanks and regards

Bivash Ghosh

+919914445701

Answer
Discuss

Answers

0

Copy / Paste works differently with PasteSpecial as compared to Cut / Paste. This syntax works for both.

Range([Source].Cut/Copy Destination:=[Target]

Your second example follows this pattern. That's why it works. Your attempt with variables doesn't, and that's why it doesn't. The code below presses your attempt into the same pattern and should therefore work (E&OE)

ActiveSheet.Range(Cells(mm, 1), Cells(LastRow1, 31)).Cut _
        ActiveSheet.Cells(LastRow2 + 1, 1)

Observe, for one, that you don't need to Select anything. Selecting is strictly for the user. Excel or VBA can just as soon write to or read from a cell as they can select it. Further observe that the Destination property is the default and can therefore be omitted. However, if you are thinking of writing both efficient and readable code you might opt for syntax as shown below.

With ActiveSheet
    .Range(.Cells(mm, 1), .Cells(LastRow1, 31)).Cut Destination:=.Cells(LastRow2 + 1, 1)
End With

Pay attention to all the elading periods which link each cell and range to the object defined in the With statement.

Discuss


Answer the Question

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