Copying and pasting info from one sheet to another using lastcol & lastrow

0
votes

I understand copying and pasting is not a recommended way to go, however am proceeding with it. I currently have a amount of data on each sheet. I am avoiding the final row when I copy the data from the rows(Works perfect). And I am avoiding the final column when I copy the columns(Works perfect).

LastRow:

Lastrow = Cells(Rows.count, "A").End(xlUp).Row - 1

LastColumn:

With ActiveSheet
lastcol = .Cells(4, .Columns.count).End(xlToLeft).Column - 1 
End With

I however need to now copy the data. I can copy the rows perfectly fine. However the columns is where I am getting caught up on. I'm quiet new to the Copy/Paste method, and am unsure how to go along of fixing this as I always used a range of cell names.

This will give me the rows needed, and all the columns needed as I am copying past the last column.

Sheets("Sheet2").Range("A4:AZ" & Lastrow).Copy Sheets(PlantArr(e)).Range("A1") 'Copy info to new sheet

How can I convert this so that AZ is actually the last column needed? Is there an easy way to go about this or will I need to set up a case to convert column number to cell value?

Answer:

gAddress = Split(Cells(1, lastcol).Address(True, False), "$") 'Converts Lastcol number into alphabet character
    letter = gAddress(0)

    Sheets("Sheet2").Range("A4:" & letter & Lastrow).Copy Sheets(PlantArr(e)).Range("A1") 'Copy info to new sheet
2
vbaexcel

2 Answers

1
votes

After a bit of digging around, I figured out that you can pull an address from a column value. I hope the code below helps anyone who would like to convert a column number to an alphabet column.

Dim gAddress
gAddress = Split(Cells(1, lastcol).Address(True, False), "$")
letter = gAddress(0)
0
votes

It's possible to specify the copy Range by explicitly stating which cells compose that range. Using 'With', you can write something like this to accomplish what you need:

Sub copy_col_and_rows()
Dim Lastrow As Integer: Dim lastcol As Integer:

With ActiveSheet
   Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
   lastcol = .Cells(4, Columns.Count).End(xlToLeft).Column - 1
End With

With Sheets("Sheet2")
   .Range(.Cells(1, 4), .Cells(Lastrow, lastcol)).Copy    Destination:=Sheets(PlantArr(e)).Range("A1")
   'Where .Cells(1,4) corresponds to "A4"
End With


End Sub