Save and Clear Contents VBA Code

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
I have 2 separate buttons already. 1 to Save data to another excel sheet and another to clear all contents.

How do I combine these 2 buttons?

Here the 2 seperated codes:


Private Sub CommandButton1_Click()

ws_output = "data"

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("C2").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("C3").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("E2").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("C5").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("E5").Value
Sheets(ws_output).Cells(next_row, 6).Value = Range("C8").Value
Sheets(ws_output).Cells(next_row, 7).Value = Range("D8").Value
Sheets(ws_output).Cells(next_row, 8).Value = Range("E8").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("F8").Value
Sheets(ws_output).Cells(next_row, 10).Value = Range("G8").Value
Sheets(ws_output).Cells(next_row, 11).Value = Range("C9").Value
Sheets(ws_output).Cells(next_row, 12).Value = Range("D9").Value
Sheets(ws_output).Cells(next_row, 13).Value = Range("E9").Value
Sheets(ws_output).Cells(next_row, 14).Value = Range("F9").Value
Sheets(ws_output).Cells(next_row, 15).Value = Range("G9").Value
Sheets(ws_output).Cells(next_row, 16).Value = Range("C10").Value
Sheets(ws_output).Cells(next_row, 17).Value = Range("D10").Value
Sheets(ws_output).Cells(next_row, 18).Value = Range("E10").Value
Sheets(ws_output).Cells(next_row, 19).Value = Range("F10").Value
Sheets(ws_output).Cells(next_row, 20).Value = Range("G10").Value
Sheets(ws_output).Cells(next_row, 21).Value = Range("C11").Value
Sheets(ws_output).Cells(next_row, 22).Value = Range("D11").Value
Sheets(ws_output).Cells(next_row, 23).Value = Range("E11").Value
Sheets(ws_output).Cells(next_row, 24).Value = Range("F11").Value
Sheets(ws_output).Cells(next_row, 25).Value = Range("G11").Value
Sheets(ws_output).Cells(next_row, 26).Value = Range("C12").Value
Sheets(ws_output).Cells(next_row, 27).Value = Range("D12").Value
Sheets(ws_output).Cells(next_row, 28).Value = Range("E12").Value
Sheets(ws_output).Cells(next_row, 29).Value = Range("F12").Value
Sheets(ws_output).Cells(next_row, 30).Value = Range("G12").Value
Sheets(ws_output).Cells(next_row, 31).Value = Range("C13").Value
Sheets(ws_output).Cells(next_row, 32).Value = Range("D13").Value
Sheets(ws_output).Cells(next_row, 33).Value = Range("E13").Value
Sheets(ws_output).Cells(next_row, 34).Value = Range("F13").Value
Sheets(ws_output).Cells(next_row, 35).Value = Range("G13").Value
Sheets(ws_output).Cells(next_row, 36).Value = Range("C14").Value
Sheets(ws_output).Cells(next_row, 37).Value = Range("D14").Value
Sheets(ws_output).Cells(next_row, 38).Value = Range("E14").Value
Sheets(ws_output).Cells(next_row, 39).Value = Range("F14").Value
Sheets(ws_output).Cells(next_row, 40).Value = Range("G14").Value
Sheets(ws_output).Cells(next_row, 41).Value = Range("C15").Value
Sheets(ws_output).Cells(next_row, 42).Value = Range("D15").Value
Sheets(ws_output).Cells(next_row, 43).Value = Range("E15").Value
Sheets(ws_output).Cells(next_row, 44).Value = Range("F15").Value
Sheets(ws_output).Cells(next_row, 45).Value = Range("G15").Value
Sheets(ws_output).Cells(next_row, 46).Value = Range("C16").Value
Sheets(ws_output).Cells(next_row, 47).Value = Range("D16").Value
Sheets(ws_output).Cells(next_row, 48).Value = Range("E16").Value
Sheets(ws_output).Cells(next_row, 49).Value = Range("F16").Value
Sheets(ws_output).Cells(next_row, 50).Value = Range("G16").Value
Sheets(ws_output).Cells(next_row, 51).Value = Range("C17").Value
Sheets(ws_output).Cells(next_row, 52).Value = Range("D17").Value
Sheets(ws_output).Cells(next_row, 53).Value = Range("E17").Value
Sheets(ws_output).Cells(next_row, 54).Value = Range("F17").Value
Sheets(ws_output).Cells(next_row, 55).Value = Range("G17").Value
Sheets(ws_output).Cells(next_row, 56).Value = Range("C18").Value
Sheets(ws_output).Cells(next_row, 57).Value = Range("D18").Value
Sheets(ws_output).Cells(next_row, 58).Value = Range("E18").Value
Sheets(ws_output).Cells(next_row, 59).Value = Range("F18").Value
Sheets(ws_output).Cells(next_row, 60).Value = Range("G18").Value
Sheets(ws_output).Cells(next_row, 61).Value = Range("C19").Value
Sheets(ws_output).Cells(next_row, 62).Value = Range("D19").Value
Sheets(ws_output).Cells(next_row, 63).Value = Range("E19").Value
Sheets(ws_output).Cells(next_row, 64).Value = Range("F19").Value
Sheets(ws_output).Cells(next_row, 65).Value = Range("G19").Value
Sheets(ws_output).Cells(next_row, 66).Value = Range("C20").Value
Sheets(ws_output).Cells(next_row, 67).Value = Range("D20").Value
Sheets(ws_output).Cells(next_row, 68).Value = Range("E20").Value
Sheets(ws_output).Cells(next_row, 69).Value = Range("F20").Value
Sheets(ws_output).Cells(next_row, 70).Value = Range("G20").Value
Sheets(ws_output).Cells(next_row, 71).Value = Range("C21").Value
Sheets(ws_output).Cells(next_row, 72).Value = Range("D21").Value
Sheets(ws_output).Cells(next_row, 73).Value = Range("E21").Value
Sheets(ws_output).Cells(next_row, 74).Value = Range("F21").Value
Sheets(ws_output).Cells(next_row, 75).Value = Range("G21").Value
Sheets(ws_output).Cells(next_row, 76).Value = Range("C22").Value
Sheets(ws_output).Cells(next_row, 77).Value = Range("D22").Value
Sheets(ws_output).Cells(next_row, 78).Value = Range("E22").Value
Sheets(ws_output).Cells(next_row, 79).Value = Range("F22").Value
Sheets(ws_output).Cells(next_row, 80).Value = Range("G22").Value
Sheets(ws_output).Cells(next_row, 81).Value = Range("C23").Value
Sheets(ws_output).Cells(next_row, 82).Value = Range("D23").Value
Sheets(ws_output).Cells(next_row, 83).Value = Range("E23").Value
Sheets(ws_output).Cells(next_row, 84).Value = Range("F23").Value
Sheets(ws_output).Cells(next_row, 85).Value = Range("G23").Value
Sheets(ws_output).Cells(next_row, 86).Value = Range("C24").Value
Sheets(ws_output).Cells(next_row, 87).Value = Range("D24").Value
Sheets(ws_output).Cells(next_row, 88).Value = Range("E24").Value
Sheets(ws_output).Cells(next_row, 89).Value = Range("F24").Value
Sheets(ws_output).Cells(next_row, 90).Value = Range("G24").Value
Sheets(ws_output).Cells(next_row, 91).Value = Range("C25").Value
Sheets(ws_output).Cells(next_row, 92).Value = Range("D25").Value
Sheets(ws_output).Cells(next_row, 93).Value = Range("E25").Value
Sheets(ws_output).Cells(next_row, 94).Value = Range("F25").Value
Sheets(ws_output).Cells(next_row, 95).Value = Range("G25").Value
Sheets(ws_output).Cells(next_row, 96).Value = Range("C26").Value
Sheets(ws_output).Cells(next_row, 97).Value = Range("D26").Value
Sheets(ws_output).Cells(next_row, 98).Value = Range("E26").Value
Sheets(ws_output).Cells(next_row, 99).Value = Range("F26").Value
Sheets(ws_output).Cells(next_row, 100).Value = Range("G26").Value
Sheets(ws_output).Cells(next_row, 101).Value = Range("C27").Value
Sheets(ws_output).Cells(next_row, 102).Value = Range("D27").Value
Sheets(ws_output).Cells(next_row, 103).Value = Range("E27").Value
Sheets(ws_output).Cells(next_row, 104).Value = Range("F27").Value
Sheets(ws_output).Cells(next_row, 105).Value = Range("G27").Value
Sheets(ws_output).Cells(next_row, 106).Value = Range("C28").Value
Sheets(ws_output).Cells(next_row, 107).Value = Range("D28").Value
Sheets(ws_output).Cells(next_row, 108).Value = Range("E28").Value
Sheets(ws_output).Cells(next_row, 109).Value = Range("F28").Value
Sheets(ws_output).Cells(next_row, 110).Value = Range("G28").Value

End Sub

Private Sub CommandButton2_Click()

Range("C2", "C3").ClearContents
Range("C5").ClearContents
Range("E5").ClearContents
Range("E2", "F2").ClearContents
Range("C8", "C28").ClearContents
Range("D8", "D28").ClearContents
Range("E8", "E28").ClearContents
Range("F8", "F28").ClearContents
Range("G8", "G28").ClearContents

End Sub
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,397
Office Version
  1. 2013
Platform
  1. Windows
Put this after the last line in the first Sub
VBA Code:
Sheets(ws_output).Cells(next_row, 110).Value = Range("G28").Value
Range("C2", "C3", "C5", "E2", "E5", "C8", "C28", "D8", "D28", "E8", "E28", "F2", "F8", "F28", "G8", "G28").ClearContents
 

jefflab1

New Member
Joined
Jul 9, 2015
Messages
14
When it comes to the Ranges. How do I write it for a single cell and for a range of cells?
Individual would be C2, C3, C5, E5
Cell Ranges: E2:F2
C8:C28
D8:D28
E8:E28
F8:F28
G8:G28

Thanks!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,397
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Range("C2", "C3", "C5", "E2:F2", "E5", "C8:C28", "D8:D28", "E8:E28", "F8:F28", "G8:G28").ClearContents
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,527
Members
410,689
Latest member
ConfuzzledThomas
Top