Solved: Formulas are not being copied correctly

[Alex550]

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

[austenr]

Just as a curiosity, why didn't you go with a for, next loop in the first part?

[Alex550]

That is the way the Macro writer did it. i was going to clean up the code as soon as i had something working.

Any ideas why the fourmulas are getting messed up??

Alex

[mbarron]

Can you post a sample of your sheet? Sanitized of sensitive data of course.

[Alex550]

OK,

i uploaded it.

Alex

[mbarron]

Hi Alex,
There is no file. Make sure the file is less than 1mb

[Alex550]

OK how about now.

[mbarron]

I've attached the results of running the macro.
I'm not sure what this means "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)"

[Alex550]

Ok,

i see what is going on. i should have given you a little history. the file starts out as a text file. i was not allowed to attach a .txt file so i saved it as .xls.

i am getting the data out of an IBM iSeries as a .txt delimited by '~'. i want the macro to make the worksheets. brfore the macro is run you will see formulas in for example rows S and T contain formulas in worksheet sopxl when they get copied they get messed up. i have attached the text file in a zip file.

Alex

[mbarron]

This line is setting the values for the cells. The key word is values. It does not copy the formulas, it copies the values.
ws2.Range("A" & NR).Resize(, 21).Value = SOP.Range("A" & FR).Resize(, 21).Value


Another problem you'll run into is when the Style number is a duplicate. Your Match function will find the first style number match and use that row's data repeatedly. This will result in your formulas being "off" for the duplicates' rows as well as the data being incorrect.

[Alex550]

Ok, i learned something. how do i get it done?
the code below worked lt was created by the macro recorder, but the Rows command does not seem to like variables. i tried puting a first row VAR and A last row Var and it just kept giving me an error. the sheets command worked with the variable Group.

if i can get a variable in the Rows command i am go to go.
do you know if this is possable and if yes the correct syntax.


Dim Group As String
Dim Frow As Long
Dim Lrow As Long

This worked
Sheets("sopxl").Select
Rows("1:80").Select
Selection.Copy
Sheets(Group).Select
Range("A1").Select
ActiveSheet.Paste

This did not
Sheets("sopxl").Select
Rows("Frow:Lrow").Select or Rows(Frow:Lrow).Select
Selection.Copy
Sheets(Group).Select
Range("A1").Select
ActiveSheet.Paste

:banghead:

[mbarron]

Try

Rows(Frow & ":" & Lrow).Select

or, as one line:


Sheets("sopxl").Rows(Frow & ":" & Lrow).Copy Destination:=Sheets(Group).Range("A1")

[Alex550]

That worked. thanks a lot.