none
Based on the Header (String) want to pick the Range to Next Sheet VBA Code Required RRS feed

  • Question

  • Hi, Want to pick Header from a Sheet1 to Sheet2 based on the Sheet1 Header.

    Eg. where ther ABC is the String i have to Pick... and Paste in Next Sheet2 in a Range.

    Col B C D E F G H I J K L M
    Header ABC_W1 ABC_W2 ABC_W3 ABC_W4 ABC_W5 ABC_W6 XYZ_WK1 XYZ_WK2 XYZ_WK3 XYZ_WK4 XYZ_WK5 XYZ_WK6

    But Now its WK1 to WK6 but it may vary to WK-5 also some time or WK-7 range differ.

    Sub Copy_Data()
        Application.ScreenUpdating = False
      Worksheets("Forecasting").Range("D6:J356").Value = Worksheets("Base").Range("B1:H350").Value
      Application.ScreenUpdating = True

    End Sub

    If the range is Same i can use this Code, but Range differs it goes wrong.

    Pl help me....

    Regards,

    Monday, February 17, 2014 11:30 AM

All replies

  • Select the first week's cell (with  ABC_W1 or XYZ_W1) on sheet "Base" and run this macro. I have assumed that Forecasting D6:JXXXX can be cleared prior to entering the values from Base

    Sub TestMacro()
        Dim rngC As Range
        Dim rngV As Range
        Dim lngR As Long
        Dim intC As Integer

        Set rngC = ActiveCell
        lngR = Cells(Rows.Count, rngC.Column).End(xlUp).Row - rngC.Row + 1
        intC = Application.WorksheetFunction.CountIf(rngC.EntireRow, Left(rngC.Value, InStr(1, rngC.Value, "_") - 1) & "*")
        Set rngC = rngC.Resize(lngR, intC)
        With Worksheets("Forecasting")
            .Range("D6:J" & .UsedRange.Cells(.UsedRange.Cells.Count).Row).ClearContents
            .Range("D6").Resize(rngC.Rows.Count, rngC.Columns.Count).Value = rngC.Value
        End With
    End Sub


    Monday, February 24, 2014 8:46 PM