Solved: Assistance Reducing to Better Code

[YellowLabPro]

Could I get some intervention here to have this written better? This was taken straight from the the macro recorder. I am copying on column of data from one workbook to another column of another workbook.

Sub CopyColumn()
'
' CopyColumn Macro
' Macro recorded 6/28/2006 by YellowLabProject
'
' Keyboard Shortcut: Ctrl+Shift+C
'
Windows("MasterImportSheetWebStore.xls").Activate
Range("B4").Select
Sheets("PCCombined_FF").Select
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Complete_Upload_File.xls").Activate
Range("B4").Select
ActiveSheet.Paste
End Sub


thanks,

YLP

[Norie]

Try this.


Dim LastRow As Long
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B4").End(xlDown).Row
.Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4")
End With

[YellowLabPro]

Thank you Norie,
That worked great!
I added two lines, but received an error in the End With line. Could you determine the cause and instruct how to correct?


Sub ColumnCopy()
Dim LastRow As Long
With Workbooks("MasterImportSheetWebStore.xls").Sheets("PCCombined_FF")
LastRow = .Range("B4").End(xlDown).Row
.Range("B4:B" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("B4")
.Range("D4: D" & LastRow).Copy Workbooks("Complete_Upload_File.xls").ActiveSheet.Range("C4")

.Cells.Columns.AutoFit
End With
End Sub

thank you again,

YLP

[compariniaa]

What was your error? I can't really find anything wrong, but I'm also not very good with VBA. sorry

but I can help you with your definition of LastRow, it could be better. The way you have it set up:

LastRow = .Range("B4").End(xlDown).Row

if there's a blank cell in the middle of your data, Excel will define LastRow as the cell above that blank cell, and you could end up writing over all the data below that cell.
And even if you don't have blank cells, this function will give you the last NONblank cell. To find the first blank cell, just append "+1" to your definition of LastRow. But an even better way to fix this would be to start from the bottom and go up:

LastRow=.Range("B65536").End(xlUp).Row+1

This method is not absolutely foolproof, but almost. The only weakness I can think of is if your data goes all the way down to B65536, but then I guess you'd have another problem besides finding the last cell :) or if for some reason you have a cell sitting by itself in the expanse between B65536 and your real last row. good luck

[YellowLabPro]

Thanks,
There actually was no error. The program delivered an erroroneous message. I rebooted and relaunched and the code ran fine.

Thanks again,

YLP