Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need assistance with code, please
Hi,
I am using Rons code to copy a range from one workbook to another and it works well. The problem I am having is that the source range is not always the same number of rows but will always have the same number of columns. So I am getting blank rows inserted into destination workbook (which happens to be a LIST). My source range is A2:AA1000, How can I change the code below so that my source range will only copy to the last row of data. Your help appreciated as always Thanks Winnie Sub Copy_To_DATA2009_Workbook() Dim SourceRange As Range Dim DestRange As Range Dim DestWB As Workbook Dim DestSh As Worksheet Dim Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'Change the file name (2*) and the path/file name to your file If bIsBookOpen_RB("DATA 2009.xls") Then Set DestWB = Workbooks("DATA 2009.xls") Else Set DestWB = Workbooks.Open("C:\Spares sales\DATA 2009.xls") End If 'Change the Source Sheet and range Set SourceRange = ThisWorkbook.Sheets("SALES1").Range("A2:AA1000") 'Change the sheet name of the database workbook Set DestSh = DestWB.Worksheets("2009") Lr = LastRow(DestSh) Set DestRange = DestSh.Range("A" & Lr + 1) 'We make DestRange the same size as SourceRange and use the Value 'property to give DestRange the same values With SourceRange Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count) End With DestRange.Value = SourceRange.Value DestWB.Close savechanges:=True With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code assistance | Excel Programming | |||
Help with code assistance | Excel Programming | |||
Assistance with VBA code on MAC | Excel Programming | |||
I need assistance getting VBA code to do the following... | Excel Programming | |||
VBA Code Assistance | Excel Programming |