Alex550
04-20-2010, 12:17 PM
I am Using VBA to copy rows from i worksheet to another the formula is good in the from sheet but is lost in the to sheet. the VBA code below what is really weird is the first line copied the formula is good(ROW 8)
Sub format()
Dim Group As String
Dim SOP As Worksheet
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long, FR As Long, NR As Long, a As Long, rng As Range
' Application.ScreenUpdating = False
Set SOP = Worksheets("sopxl")
' get the group name
Sheets("sopxl").Select
Sheets.Add
Sheets("sopxl").Select
Group = SOP.Cells(3, "A").Value
' Rename the worksheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = Group
' Copy the first seven rows
Sheets("sopxl").Select
Rows("1:1").Select
Selection.Cut
Sheets(Group).Select
Rows("1:1").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("2:2").Select
Selection.Cut
Sheets(Group).Select
Rows("2:2").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("3:3").Select
Selection.Cut
Sheets(Group).Select
Rows("3:3").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("4:4").Select
Selection.Cut
Sheets(Group).Select
Rows("4:4").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("5:5").Select
Selection.Cut
Sheets(Group).Select
Rows("5:5").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("6:6").Select
Selection.Cut
Sheets(Group).Select
Rows("6:6").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("7:7").Select
Selection.Cut
Sheets(Group).Select
Rows("7:7").Select
ActiveSheet.Paste
Set ws2 = Worksheets(Group)
Set rng = SOP.Range("A:A")
LR = SOP.Cells(Rows.Count, "V").End(xlUp).Row
For a = 8 To 250 Step 1
If SOP.Cells(a, "V") <> "H" Then
FR = WorksheetFunction.Match(SOP.Range("A" & a), rng, 0)
NR = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws2.Range("A" & NR).Resize(, 21).Value = SOP.Range("A" _ & FR).Resize(, 21).Value
Else
a = 250
End If
Next a
End Sub
Sub format()
Dim Group As String
Dim SOP As Worksheet
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long, FR As Long, NR As Long, a As Long, rng As Range
' Application.ScreenUpdating = False
Set SOP = Worksheets("sopxl")
' get the group name
Sheets("sopxl").Select
Sheets.Add
Sheets("sopxl").Select
Group = SOP.Cells(3, "A").Value
' Rename the worksheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = Group
' Copy the first seven rows
Sheets("sopxl").Select
Rows("1:1").Select
Selection.Cut
Sheets(Group).Select
Rows("1:1").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("2:2").Select
Selection.Cut
Sheets(Group).Select
Rows("2:2").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("3:3").Select
Selection.Cut
Sheets(Group).Select
Rows("3:3").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("4:4").Select
Selection.Cut
Sheets(Group).Select
Rows("4:4").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("5:5").Select
Selection.Cut
Sheets(Group).Select
Rows("5:5").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("6:6").Select
Selection.Cut
Sheets(Group).Select
Rows("6:6").Select
ActiveSheet.Paste
Sheets("sopxl").Select
Rows("7:7").Select
Selection.Cut
Sheets(Group).Select
Rows("7:7").Select
ActiveSheet.Paste
Set ws2 = Worksheets(Group)
Set rng = SOP.Range("A:A")
LR = SOP.Cells(Rows.Count, "V").End(xlUp).Row
For a = 8 To 250 Step 1
If SOP.Cells(a, "V") <> "H" Then
FR = WorksheetFunction.Match(SOP.Range("A" & a), rng, 0)
NR = ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws2.Range("A" & NR).Resize(, 21).Value = SOP.Range("A" _ & FR).Resize(, 21).Value
Else
a = 250
End If
Next a
End Sub