Adding line into Excel with macro

Question

I have this code and I am trying to get it to add a line in when copying the information accross. The issue I have is that it adds a line in between them and scrambles the information. I have a template worksheet with a total on the bottom and basicly want it pushed down as the lines are enetered.

Any help would be great

Sub SummurizeSheets()
Dim ws As Worksheet, wsSummary As Worksheet
Dim c As Range

Range("A4:D31").Select
Selection.ClearContents

Application.ScreenUpdating = False
Set wsSummary = Sheets("Summary")
' Set destination cell
Set c = wsSummary.Range("A4")

For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        ActiveCell.EntireRow.Insert
        ws.Range("D1").Copy
        c.PasteSpecial (xlPasteValues)
        ws.Range("E4").Copy
        c.Offset(0, 1).PasteSpecial (xlPasteValues)
        ws.Range("J39").Copy
        c.Offset(0, 2).PasteSpecial (xlPasteValues)
        ' Move destination cell one row down
        Set c = c.Offset(1, 0)
    End If
Next ws
Application.ScreenUpdating = True
End Sub

Try changing ActiveCell.EntireRow.Insert with c.EntireRow.Insert and remove this line Set c = c.Offset(1, 0). — L42
– L42, Commented Mar 25, 2014 at 2:59
No luck. I inserted and removed as suggested. It either only copy's information from one worksheet and adds the right amount of rows. Or if i add it to another place in the coding it doesn't add the lines and deletes the "Grand Total" Text with my price at the bottom becuase it didnt add any rows. — Aussiehelper
– Aussiehelper, Commented Mar 25, 2014 at 3:11
Ok, it seems even if you Set your Range, it automatically adjust when you use Insert Method. So when you insert, the value of your c becomes Range("A5"). To write c (A4) correctly, use Offset(-1,0). See my post. — L42
– L42, Commented Mar 25, 2014 at 3:32
Why Insert a new row if your offsetting c down a row each time. I took out this line: ActiveCell.EntireRow.Insert and everything worked fine. — Automate This
– Automate This, Commented Mar 25, 2014 at 3:35

L42 · Accepted Answer · 2014-03-25 03:30:19Z

2

Try this then:

Sub SummurizeSheets()
Dim ws As Worksheet, wsSummary As Worksheet
Dim c As Range

Application.ScreenUpdating = False
Set wsSummary = Sheets("Summary")
Set c = wsSummary.Range("$A$4")

For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        c.EntireRow.Insert xlDown, xlFormatFromLeftOrAbove
        Set c = c.Offset(-1, 0)
        ws.Range("D1").Copy
        c.PasteSpecial xlPasteValues
        ws.Range("E4").Copy
        c.Offset(0, 1).PasteSpecial xlPasteValues
        ws.Range("J39").Copy
        c.Offset(0, 2).PasteSpecial xlPasteValues
    End If
Next ws
Application.ScreenUpdating = True
End Sub

answered Mar 25, 2014 at 3:30

L42

19.8k11 gold badges47 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Automate This Over a year ago

+1 - This solves the problem if the OP needs a list from top to bottom. If he doesn't then they can just remove the insert line in the original code.

Aussiehelper Over a year ago

This worked. Not sure why but is seems to be making the text it has copied like the original e.g. Bold and Blue. No big issue ill just format the style after it has completed. Thank you very much for your help.

Aussiehelper Over a year ago

I fixed it by adding a line above it and changing the style. I change Set c = wsSummary.Range("$A$4") to Set c = wsSummary.Range("$A$5"). Made Row 4 the style i wanted it to and now it all works fine.

Collectives™ on Stack Overflow

Adding line into Excel with macro

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related