Macro - copy and paste cells from one to another

[gavster89]

Hey,

I have created a macro which takes a list from sheet 1 and copies to sheet 2.

Once in sheet 2 i am doing the following in column c (=sum(a1+b1)), i am doing this for however many items were copied from sheet 1 to 2.

The problem occurs when i add more information into sheet 1. Once the macro runs it copies everything from sheet 1 to 2 and adds the cells a1+b1 all the way down to a100+b100 (copied 100 items over). Once i try to copy more than 100 items (200 for example) my Macro doesnt automatically add these and include them in sheet 2. It copies all of the information over but the =sum(a1+b1) only seems to be working down to a100+b100.

Any ideas please?

Thanks

[SteveG]

Hi,

Modify this and add to your code to determine the last row to copy down to.

Please Login or Register  to view this content.

HTH
Steve

[gavster89]

Thanks SteveG,

I am new to Macros and dont understand what i need to change to the code above. I have included this in my code and nothing has changed, i know im suppose to change something in your code but not sure what exactly.

Thanks again for the help

[SteveG]

Can you post your code?

Thanks,
Steve

[gavster89]

End Sub
Sub CopyDown()
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Value = "=sum(C2+D2)"
Range("E2").Select
Selection.Copy
Range("E2:E" & LastRow).PasteSpecial
Application.ScreenUpdating = True
End Sub

The rest of my code does what i want it to do, this is your code you told me to include?

Thanks again

[SteveG]

Before your code's "End Sub" did you put in "Call CopyDown"? If not, it's not being triggered. The reason I asked for your code that is working is so I add my code to it in the correct place.

Steve

[gavster89]

Sub Run()
'
' Run Macro
'

'
Sheets("Sheet2").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Ferro").Select
ActiveSheet.Paste
Range("A:B,D:D,G:H").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("E1").Select
ActiveCell.FormulaR1C1 = "Total"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]+RC[-1])"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E27")
Range("E2:E27").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "Percentage"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
End Sub
Sub CopyDown()
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Value = "=sum(C2+D2)"
Range("E2").Select
Selection.Copy
Range("E2:E" & LastRow).PasteSpecial
Application.ScreenUpdating = True
End Sub

[SteveG]

Hi,

You're not calling the second macro with your first so when it gets to the end of yours, it simply exits the sub. See below where I added "Call CopyDown".

Please Login or Register  to view this content.

HTH

Steve

[SteveG]

So,
Run()
'
' Run Macro
'

'
Sheets("Sheet2").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Ferro").Select
ActiveSheet.Paste
Range("A:B,D:D,G:H").Select
Range("G1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("E1").Select
ActiveCell.FormulaR1C1 = "Total"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]+RC[-1])"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E27")
Range("E2:E27").Select
Range("F1").Select
ActiveCell.FormulaR1C1 = "Percentage"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Calibri"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Call CopyDown
End Sub

Sub CopyDown()
Application.ScreenUpdating = False
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("E2").Value = "=sum(C2+D2)"
Range("E2").Select
Selection.Copy
Range("E2:E" & LastRow).PasteSpecial
Application.ScreenUpdating = True
End Sub

[gavster89]

Thats great, thanks for your help.