Complex excel macro

Question

Complex excel macro

I create a sophisticated distribution sheet where I can track calls across our network to various clients according to each week. I have two sheets: "raw_data" and "Sheet1".

I copy the data from our server to "Sheet1" in a vertical layout and run a macro that removes unneeded lines of data.

In "raw-data", call volumes should be sorted according to client name in a horizontal layout. What I want on this sheet is at least one button with a macro assigned, which will search through column A for the next free cell, insert a new row, add the next date in sequence (03/19/2012, 03/26/26 2012, ...) and finally copy the data from "Sheet1" and paste the values under the appropriate column headers (then I want to delete the contents of "Sheet1" but keep the values in "raw_data").

I know that VBA inserts a new line at a specific point, but not at the end of a long list. Here is the code I have:

Sub Insertrow()
    Rows("33:33").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Any help would be great or even similar examples would be great so I can edit them myself so I can learn.

Thank!

Mark

UPDATE

I could not add the code to the comment in the correct format. So here goes again ...

Sub Insertrow()
        'Selects next empty cell in column A
        ActiveWindow.ScrollRow = 2
        Range("A2").Select
        Selection.End(xlDown).Offset(1, 0).Select
        'Inputs the next date
        Dim n As Long, k As Long
        Application.ScreenUpdating = False
        Rng = 1
        Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
        k = ActiveCell.Offset(-1, 0).Row
        'copies the formula 1 cell from the left (Column A) to the row below
        n = Cells(k, 1).End(xlToLeft).Column
        Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown
End Sub

Now I want to do a column search on Sheet1 for a specific line of text (header from raw_data), copy the value from that line which is under the date generated from the code (see above). I could probably use code similar to the above or a nested if statement, but the values from the Sheet will be removed weekly. Is this possible or am I just dreaming about it in my head?

+3

excel-vba excel

Mark 04 Apr 12 at 11:08

source to share

1 answer

Siddharth Rout · Answer 1 · 2012-04-04T11:21:37+0000

Note, if the data is not in tabular format (i.e. it is not listobject

, then you don't need to insert a row), which you can simply write to the last row. To write data to the last line, you can use something like this

Sub Sample()
    Dim ws As Worksheet
    Dim wslRow As Long

    Set ws = Sheets("Sheet1")

    wslRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1

    ws.Range.Range("A" & wslRow).Value = "Blah Blah"
End Sub

After that, you can sort the data in the date column.

If the data is in tabular format, you can use this technique and then sort the data.

Runtime error '91' while executing a macro

NTN

Sid

Complex excel macro

More articles: