CODEWITHSUNDEEP
excelvba
Mark
Mark

Reputation: 197

complex excel macro

I am creating a complex spread sheet, where I can monitor calls through our network to different customers according to each week. I have two sheets, "raw_data" and "Sheet1".

I copy data from our server to "Sheet1" into a vertical layout and run a macro that strips away unnecessary rows of data.

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

I know the VBA to insert a new row at a specific point, but not at the end of the long list. Here's 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.

Thanks!

Mark

UPDATE

I couldn't seem to add the code to a 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

What I want to do now is search through a column on a Sheet1 for a specific string of text (the header from raw_data) copy the value from this row that is under the date that was generated from the code (above). I could probably use a code similar to that above or a nested if statement, but the values from Sheet one will be deleted on a weekly basis. Is this possible or am I just dreaming it up in my head?

Upvotes: 3

Views: 2365

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Mark, if the data is not in a tabular format (i.e it is not a listobject then you do not need to insert the row) you can simply write to the last row. To write data to the last row, 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

You can then if required sort the data on the date column.

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

Runtime error '91' on macro execution

HTH

Sid

Upvotes: 2

Related Questions