Excel Forum

Code: Select all

Sub UpdateData()
    Dim rAll As Range
    Dim r As Range
    Dim sh As Worksheet
    Dim i As Integer
    i = 2
    With Sheets("Data_BCE")
        Set rAll = .Range("u4", .Range("u" & .Rows.Count).End(xlUp))
        For Each r In rAll
            If r.Value = "" Then Exit Sub
            Set sh = Worksheets(i)
            With sh
                .Range("a2:b2").Value = r.Resize(1, 2).Value
                .Name = r.Offset(0, 1).Value
                .Range("b4").Resize(1, 9).Value = _
                    r.Parent.Cells(4, "b").Resize(1, 12).Value
            End With
            i = i + 1
        Next r
    End With
End Sub

Code: Select all

Sub KeepData_Fore()


    Range("B4:E4").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Sheets("Foreign_A").Select

    Range("A3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range("E3").Select

    Sheets("Data_BCE").Select

    Range("G4:I4").Select

    Range(Selection, Selection.End(xlDown)).Select

    Application.CutCopyMode = False

    Selection.Copy

    Sheets("Foreign_A").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    Range("F1").Select

    Sheets("Data_BCE").Select

    Range("J1").Select

    Application.CutCopyMode = False

End Sub

Code: Select all

 
    Range("B4:E4").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Sheets("Foreign_A").Select

    Range("A3").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False