Copy Data from one Worksheet to Another

[elsuji]

Dear Team,

I am copying data from one work sheet to another work sheet.

I am having 3 work sheets (Data, Sheet6 & Sheet7). my main data is in sheet "Data" and the values copies to Sheet6 & Sheet7.

For example From "Data" ("8 to 33") to Sheet6 ("11 to 36") & "Data" ("41 to 71") to Sheet7 ("11 to 41").

And i ad updating the sheet6 ("11:36") format copying from row 11.

When ever there is value on my "Data" sheet this will work properly. But if suppose my "Data" ("8"33") is empty my format is not updating.

And once again i entered the value on the row and try to copy the values. The values are copying properly, but the format is not updating.

I want when there is the value in the particular row, then the code should copy to other sheet otherwise it wont try to copy.


' For coppy the dataDim aaa As String, shArr
aaa = ActiveSheet.Name
shArr = Array("Sheet6", "Sheet7")
Application.ScreenUpdating = False
For ii = LBound(shArr) To UBound(shArr)
With Sheets(shArr(ii))
Sheets("sheet6").Range("B11:G36").Value = Sheets("Data").Range("A8:F33").Value
Sheets("sheet7").Range("B11:G41").Value = Sheets("Data").Range("A41:F71").Value
End With
Next ii
Sheets(aaa).Activate
Application.ScreenUpdating = True


'For copy the same format
Worksheets("Sheet6").Range("B12:G12").Copy
Worksheets("Sheet6").Range("B13:G73").PasteSpecial xlPasteFormats
Worksheets("Sheet7").Range("B12:G12").Copy
Worksheets("Sheet7").Range("B13:G43").PasteSpecial xlPasteFormats


'For alignment
Dim j As Long, lr As Long, a As String, LastRow As Long
a = ActiveSheet.Name
Application.ScreenUpdating = False
For j = 6 To 7
With Sheets("Sheet" & j)
lr = .Range("B:B").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
.Range("B" & lr).Resize(186).EntireRow.Delete
.Cells(.Cells(.Rows.Count, 2).End(xlUp).Row, 2).Resize(, 6).Borders(xlEdgeBottom).Weight = xlMedium
End With
Next j
Sheets(a).Activate
Application.ScreenUpdating = True

Kindly help me to solve this problem.