Copying Data from One Workbook to Another

[howard]

I have 2 workbooks, one named BR1VOA and the other BR1 New Vehicles

I set up a macro below to copy the data from Br1VOA1.xls to BR1 New Vehicles.xls


The macro works well where there is data in BR1VOA1, but if there is no data, i.e all the cells are blank, it comes up with run time error 1004, 'The information cannot be pasted because the copy area & paste area are not the same area & shape"

It would be appreciated if you could assist




Sub Copy_Data()

Windows("Br1VOA.xls").Activate
Range(Cells(2, "a"), Cells(2, "a").End(xlDown).Offset(, 2)).Copy
Windows("BR1 New Vehicles.xls").Activate
Cells(6, "a").PasteSpecial
Range("A1").Select
With Range("A:A")
.HorizontalAlignment = xlLeft
End With


Howard

 

[Richard Schollar]

Hi Howard

Since you are copying from row 2, you could first check what the last row of data is in column A (this assumes that every record in the sheet will have a populated value in column A):

Dim lngLastRow As Long
With Workbooks("Br1VOA.xls").WorkSheets(1)
  lngLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
  If lngLastRow > 1 And lngLastRow < 65530 Then
    .Range("A2:A" &  lngLastRow).Offset(0,2).Copy Destination:= _
     Workbooks("BR1 New Vehicles.xls").Worksheets(1).Range("A6")
  Else
    MsgBox "Range out of bounds!"
  End If
End With
Workbooks("BR1 New Vehicles.xls").Worksheets(1).Range("A6:A" & lngLastRow+6-1).HorizontalAlignment = xlLeft

 

[howard]

Hi Richard

Thanks for the help. Code works perfectly

Regards

Howard

 

[howard]

Hi Richard

I have just checked your code on Another bookbook rovvoa1)where the data in Column A to C from row 2 onwards in supposed to be copied in cell A6 onwards on Workbook Rov VOA's.Newvehicles.xls

Only the data from C2 onwards is copied into the destination workbook

See Source & Destination Workbook

Your assistance will be most appreciated

Regards

Howard


Source Workbook

rovvoa1.xls
	A	B	C	D
1	StockNo	SupplyTo	Balance
2	467659	MRKOSEI-AMANKWAH	690
3	474876	MRLFOSSATI	7977.17
4	752432	MRNGODENDAAL	20533.62
5
Nissvoa1

Rov VOA's.New Vehicles.xls
	A	B	C	D
1		RovNewVehicleVOA'sasat07/07/2008
2
3
4
5	StockNo.	Customer	Amount	Date
6	690
7	7977.17
8	20533.62
9
Sheet1

 

[Richard Schollar]

Hi Howard

Apologies - I misinterpreted your original code. Amend to:

Dim lngLastRow As Long
With Workbooks("Br1VOA.xls").WorkSheets(1)
  lngLastRow = .Cells(.Rows.Count,"A").End(xlUp).Row
  If lngLastRow > 1 And lngLastRow < 65530 Then
    .Range("A2:A" &  lngLastRow).Resize(,3).Copy Destination:= _
     Workbooks("BR1 New Vehicles.xls").Worksheets(1).Range("A6")
  Else
    MsgBox "Range out of bounds!"
  End If
End With
Workbooks("BR1 New Vehicles.xls").Worksheets(1).Range("A6:A" & lngLastRow+6-1).HorizontalAlignment = xlLeft

 

[howard]

Hi Richard

Thanks for the help

Much appreciated


Howard