CODEWITHSUNDEEP
vbaexcel
mikeloveta
mikeloveta

Reputation: 13

Copy last cell range with data

I can't get the last section of my code to read the last cells in columns F through L with data. There will be blank cells above them. Then paste to the next available line in destination. Sub addddddddddddddd() ' ' addddddddddddddd Macro ' Dim LR As Long, LC As Integer Dim lastRow As Long Dim fNameAndPath As Variant, wb As Workbook Dir "C:\User\keym\Desktop\Timecards" fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (.), .", Title:="Select File To Be Opened") If fNameAndPath = False Then Exit Sub Set wb = Workbooks.Open(fNameAndPath)

wb.Activate
Range("D1").Select
Selection.Copy
Windows("Vacation-Sick-Summary.xls").Activate
Range("B10000").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste

wb.Activate
Range("F3:L3").Select
Selection.Copy
Windows("Vacation-Sick-Summary.xls").Activate
Range("B10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
For Each C In Selection
    If C.Value = "" Then C.Value = "-"
    C.HorizontalAlignment = xlCenter
Next C


wb.Activate
LR = Cells(Rows.Count, 1).End(xlUp).Row              '?
LC = Cells(1, Columns.Count).End(xlToLeft).Column    '?
Selection.Copy
wb.Close
Windows("Vacation-Sick-Summary.xls").Activate
ActiveSheet.Paste
For Each C In Selection
    If C.Value = "" Then C.Value = "-"
    C.HorizontalAlignment = xlCenter
Next C

End Sub

Upvotes: 1

Views: 2589

Answers (1)

barryleajo
barryleajo

Reputation: 1952

Range("B" & Rows.Count).End(xlUp).Offset(1, -1).Select

or whichever column you require.

In your last section of your code: check the Selection your are copying; check also that you mean to test for the last row of data in col A and for the last column of data along row 1.

Upvotes: 1

Related Questions