Cell Number to be stored in a variable

[MI man]

Hello everyone,

I have a table (say Col A to Col G) in an Excel spreadsheet and each column has some data. I need to copy the data and paste it into another workbook. All the columns may have different ranges, but Column A must be taken as a reference to cut-off the copying of data.

Assume that Column A has data from A22 to A40, col B has from A22 to A50, col c has A22 to A45, col D has A22 to A60....and so on.
Now, each column has to be copied and pasted (pastespecial) into another workbook in the respective columns.
All I need is to find the last row of Col A (A45 in this case) and use the number 45 as reference for all the other columns. I mean the data for all the columns should be copied only until the 45th row (B45, C45, D45...and so on).

I am using Excel 2003

Please help me out.

 

[Brianwarnock]

The code in the first Sub illustrates how to do it all by code the second how to reurn the last row to a Cell in a worksheet.

Brian

Sub copyarea()

' Coded by Brian Warnock 4/Feb/2012
Dim lastrow As Long

Application.CutCopyMode = False
Sheets("sheet1").Select
lastrow = Range("A22").End(xlDown).Row
Range(Cells(22, "A"), Cells(lastrow, "G")).Copy

Workbooks("book2").Activate
Sheets("Sheet2").Range("A22").PasteSpecial Paste:=xlPasteAll
     
Application.CutCopyMode = TrueEnd Sub

Sub rownumber()

With Sheets("sheet1").Range("A22")
Sheets("sheet1").Range("H23") = Range("A65536").End(xlUp).Row
End With

End Sub

 

[MI man]

Thank you Brian.
However, I have a twist now.
This code works, but I am making a macro in a 3rd workbook and when I am running with the code, it is selecting the range from the 3rd workbook and not the one from which it has to take it up.
Please see the code below:

Private Sub CommandButton1_Click()
Dim LR As Long
Workbooks("AT").Activate
Sheets("Revenue").Select
LR = Range("B22").End(xlDown).Row
Range(Cells(22, "B"), Cells(LR, "B")).Copy
Workbooks("TestV1").Activate
Sheets("Rev").Select
Range("A6").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
End Sub

I have written this code in a new workbook that should contain only the code.

 

[Brianwarnock]

I don't know it works for me.
Just to be clear this code is in a third book not called AT or TestV1 and it does not have sheet called Revenue .

Have you stepped through the code to see if LR has the value that it should, blank cells in row B can be a problem I have assumed all cells in the range have data.

If the books were not open you would get a subscript out of range error so it's not that.

Brian

 

[MI man]

Hi Brian,
Yes, the third book does not contain any sheet as you have named.

Anyway, I have figured out the solution. Here it is:

Private Sub CommandButton1_Click()
Dim LR As Long

With Workbooks("AT").Worksheets("Revenue")
LR = .Range("B22").End(xlDown).Row
.Range(.Cells(22, "B"), .Cells(LR, "B")).Copy
End With

With Workbooks("TestV1").Worksheets("Rev")
.Range("A6").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
End With
End Sub

Thanks a lot for your help. Your code had opened my mind.

 

[Brianwarnock]

Hmm I wonder why you needed the With construct for this and not the other, and I for neither !

Having said that sometimes I have need the full name for the workbooks statement

Workbooks("book name.xls").Activate and sometimes not, Excel help shows both versions in help depending on whether you search for workbooks or activate

I suppose the safe option is always to do it the long way and leave nothing to defaults.

Ps well done

Brian