PDA

View Full Version : Probelm with copy data between tow sheets caused format property



Nader
02-24-2008, 06:35 AM
I tried this code to copy the data form sheet1 to material sheet
But if the format of sheet1( font, size of font ..etc) is different between sheet1 and
Material the code doesn't work well . It left many rows between the data in Material sheet.How to fix this problem

Dim myRange As Range
Dim R As Long
Dim cell As Range

Set myRange = Worksheets("sheet1").Range("A1:A100") '??????
For Each cell In myRange
R = Sheets("Material").UsedRange.Rows.Count
If R <> 1 Or Sheets("Material").Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 8).Copy Sheets("Material").Range("a" & R)


Next cell

Bob Phillips
02-24-2008, 06:48 AM
Dim myRange As Range

Set myRange = Worksheets("sheet1").Range("A1:A100") '??????
myRange.Copy Sheets("Material").Range("A1")

Nader
02-24-2008, 09:03 AM
Your code is work, But I didn't show all the code. I'm sorry
I couldn't use it with all code, because all code has some conditions.


Dim myRange As Range
Dim R As Long
Dim cell As Range

Set myRange = Worksheets("sheet1").Range("A19:A100")
For Each cell In myRange
If IsError(Application.Match(cell.Value, Sheets("Material").Columns(1), 0)) _
Or IsError(Application.Match(cell.Offset(0, 1).Value, Sheets("Material").Columns(2), 0)) _
Or IsError(Application.Match(cell.Offset(0, 2).Value, Sheets("Material").Columns(3), 0)) _
Or IsError(Application.Match(cell.Offset(0, 3).Value, Sheets("Material").Columns(4), 0)) Then

R = Sheets("Material").UsedRange.Rows.Count
If R <> 1 Or Sheets("Material").Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 8).Copy Sheets("Material").Range("a" & R)

End If
Next cell