pike OzMV Rabbitoh

  • Member since Apr 9th 2007
  • Last Activity:
Posts
6,059
Likes Received
5
Points
18,530
Trophies
1
Profile Hits
2,843
  • Hey Pike, you good?


    I need some help again please. Below is a VBA which uses two worksheets and was wondering how I manipulate the code to keep all destinayion formatting.


    Thanks in advance.


    Garth



    Sub UpdateEWRDSDR()

    Worksheets("EW-RDS-DR").Select


    ' CLEAR EXISTING DATA IN EWRDSDR

    Range("b6:k1000").ClearContents


    ' COPY UPDATED DATA IN 320 AND PASTE IN EWRDSDR

    Dim rng1 As Range

    Dim n As Long

    n = Sheet1.Range("A1").Value

    Set rng1 = Sheets("EW-RDS-DR").Cells(Rows.Count, 2).End(xlUp).Offset(5, 0)

    Sheets("320").Range("N9:Q" & n).Copy Destination:=rng1


    ' COPY COLUMN C & PASTE IN COLUMNS F,H & J

    Range("C6:C1000").Copy Range("f6:f1000")

    Range("C6:C1000").Copy Range("h6:h1000")

    Range("C6:C1000").Copy Range("j6:j1000")


    ' CHANGE RELEVANT SECTION NUMBERS

    Range("H6:H1000").Replace What:="320", Replacement:="330"

    Range("J6:J1000").Replace What:="320", Replacement:="340"


    ' VLOOKUP QTYS IN 320 & PASTE IN EWRDSDR USING VLOOKUP

    Dim ws1 As Worksheet, ws2 As Worksheet

    Dim lr As Long

    Dim rng As Range

    Set ws1 = Sheets("EW-RDS-DR")

    Set ws2 = Sheets("320")

    lr = ws1.Cells(Rows.Count, "F").End(xlUp).Row

    Set rng = ws2.Range("O11:R3000")


    'This line will place the value in EWRDSDR in the range "G6:H" & lr where lr is the last row with data in col. F

    ws1.Range("G6:G" & lr).Formula = "=IFERROR(VLOOKUP(F6," & ws2.Name & "!" & rng.Address & ",4,FALSE),"""")"


    ' COPY COLUMN G & PASTE IN COLUMNS I & K

    Range("G8:G1000").Copy Range("I8:I1000")

    Range("G8:G1000").Copy Range("K8:K1000")


    ' CHANGE RELEVANT SECTION NUMBERS

    Range("I8:I1000").Replace What:="320", Replacement:="330"

    Range("K8:K1000").Replace What:="320", Replacement:="340"


    End Sub