0

I have a sheet with a range of A12:n112, and within this range column A will be "0" OR "1" based on other criteria. (I have code which sorts the data so that all the rows with a 1 are at the top of this range - this works fine)

I have another bit of code which I need code to copy cells B:L in a row which column A has a "1" in to the first blank row in another sheet. This then generates a number in the second sheet and this number is then copied back into the original sheet in the same row in column M.

When I have fixed ranged I can do this without any problems, but I am trying to do this dynamically as the data entered into B through L will differ everytime the sheet is used.

Can anyone help, here is my NEW code

Dim lr As Long
lr = Sheets("Data Entry").Cells(Rows.Count, "A").End(xlUp).Row

    For r = lr To 2 Step 1
    If Range("AB" & r).Value = "1" Then
        Rows(r).Copy.Range ("A" & lr2 + 1)
        Windows("Serialisation Log.xlsx").Activate
        Sheets("SNo Log").Select
        Range("D" & Rows.Count).End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Windows("Serialisation Log.xlsx").Activate
        Sheets("SNo Log").Select
        Range("A" & Rows.Count).End(xlUp).Offset(-1).Select
        Selection.Copy
        Range("A" & Rows.Count).End(xlUp).Offset(1).Select
        ActiveSheet.Paste
        Range("A" & Rows.Count).End(xlUp).Offset(0).Select
        Selection.Copy

        Windows("Serialisation Generator rev 1.xlsm").Activate
        Worksheets("Data Entry").Select
        Range("N").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    End If
    If Range("AB" & r).Value = "0" Then
       Range("I4").Select
       ActiveCell.FormulaR1C1 = "Serial No. Issue complete for this OA"
    End If
    Range("F5").Select
Next r

This seems to run without errors, but doesn't copy or paste anything - any tips or advice. (please note I have changed this question and code massively so the initial answers below may not longer apply.

Any assistance is greatly appreciated.

0m3r
  • 12,286
  • 15
  • 35
  • 71
RobertW081171
  • 77
  • 1
  • 12
  • 5
    Share your code - this is not a code writing service! Thanks – urdearboy Oct 31 '18 at 17:03
  • Add the code to the question please – urdearboy Nov 01 '18 at 13:17
  • code now added - I had forgotten to do this yesterday when I was writing the post. I think I will need to set this up using INteger, but cannot remember how to do this, and ultimately this would allow me to loop the instruction which is in words... Find row with a 1 in cell A, copy bells B to L and open other spreadhseet. Find the first blank cell and past from cell D the columns copied. Then from column A in this sheet copy the new number generated and copy this back into row M of the first sheet. Then repeat down for each row which will have a 1 in cell A until it finds a zero & stop – RobertW081171 Nov 01 '18 at 13:25
  • 1
    `AutoFilter` and `SpecialCells(xlCellTypeVisible)` are your good friend here :) You can create the range with only 1 in column A as variable and then loop through, paste into the next sheet and then write the number back to the original sheet – Scott Holtzman Nov 01 '18 at 13:28
  • So question I have is how would I approach that, I haven't used either of these in code before? Also how would I stop the loop from simply copying the same row over and over again? – RobertW081171 Nov 01 '18 at 14:23
  • Note: I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) especially if you are new to code VBA. – Pᴇʜ Nov 05 '18 at 08:22
  • Wizhi - I cannot get the set command to work for my second workbook (I've had to use ThisWorkbook for the first workbook unfortunately - but as the code will always sit in this one that if probably OK). My code for setting the variable for the 2nd workbook is as below, I think the syntax is right, but cannot get it to work and keep getting an "Object required" error! Set wkb2 = Workbook = ("\\srveurfcl03.nov.com\IS-GBR-GLBISETNRegister$\Serial No Trial\Serialisation Log.xlsx") – RobertW081171 Nov 05 '18 at 14:26

0 Answers0