How to link command button macros

[Bushman]

Hello, I am in need of assistance with a spreadsheet I've been working on (with help from MREXCEL, ty).
Firstly I'm trying to link three macros which I had set up seperately but realise they can run together with one prompt, how do I do this?
Secondly I have been trying to get the DatedSaveData macro (that I tried my first record macro shortcut as I know very little VB) to paste a range of copied values into the first empty column on a sheet I have called "Saved" but so far I am chasing my tail, please help!

Sub ViewData_Click()
 'Unhide cells
    Sheet3.Columns("H:J").EntireColumn.Hidden = _
    Not Sheet3.Columns("H:J").EntireColumn.Hidden
End Sub
Private Sub CommandButton1_Click()
    ' Clear cells in a range
    Sheets("Sheet1").Range("J10:L56").ClearContents
End Sub

Sub DatedSaveData2()
    ' 
    Sheets("Saved").Select
    ' Insert Date at the top of the first empty column
    ActiveCell.FormulaR1C1 = "=NOW()"
    ' Copy data from sheet "All"
    Sheets("All").Select
    Range("K11:K39").Select
    Selection.Copy
    ' Paste to first empty cell below date on sheet "saved"
    Sheets("Saved").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    'Copy data from sheet "Hoodies"
    Sheets("Hoodies").Select
    Range("K40:K56").Select
    Application.CutCopyMode = False
    Selection.Copy
    ' Paste to first empty cell below last paste
    Sheets("Saved").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
    ' Select sheet "Hoodies"
    Sheets("Hoodies").Select
End Sub

 

[snowblizz]

Made a few fixes, see if this works for you.

Sub DatedSaveData2()
    ' 
    Sheets("Saved").Select
    ' Insert Date at the top of the first empty column
    ActiveCell.FormulaR1C1 = "=NOW()" ' <- will always be A1 not the first empty cell
    ' Copy data from sheet "All"
    Sheets("All").Range("K11:K39").Copy destination:= _
Sheets("Saved").Range("A" & Sheets("Saved").rows.count).End(xlup).Offset(1, 0)
    ' Paste to first empty cell below date on sheet "saved"
    
    'Copy data from sheet "Hoodies"
    Sheets("Hoodies").Range("K40:K56").Copy destination:= _
Sheets("Saved").Range("A" & Sheets("Saved").rows.count).End(xlup).Offset(1, 0)
    ' Paste to first empty cell below last paste
 
    ' Select sheet "Hoodies"
    Sheets("Hoodies").Select
End Sub

 

[Bushman]

Thx Snowblizz, that works better but the second press of the button went in the first free cell under the first set of data rather than in the top of the first free column, how can I change that?

 

[snowblizz]

Oh, columns eh. Try this, hopefully works.

Sub DatedSaveData2()
    ' 
    Sheets("Saved").Select
    ' Insert Date at the top of the first empty column
    ActiveCell.FormulaR1C1 = "=NOW()" ' <- will always be A1 not the first empty cell

    Sheets("All").Range("K11:K39").Copy destination:= _
Sheets("Saved").Range("A" & Sheets("Saved").columns.count).End(xlleft).Offset(0, 1)
 
    Sheets("Hoodies").Range("K40:K56").Copy destination:= _
Sheets("Saved").Range("A" & Sheets("Saved").columns.count).End(xlleft).Offset(0, 1)


    Sheets("Hoodies").Select
End Sub</pre>

 

[Bushman]

Thanks for trying again Snowblizz.
Now I get a message box which just says 400 in it. The date appeared in cell N24 on an empty sheet? How can I change this line....

ActiveCell.FormulaR1C1 = "=NOW()"

...so that it puts the date at the top of the first free cell and the rest of the copied values are pasted below in the same column?

 

[Bushman]

Also, does anyone know how to link these so they are either one macro or so the command button activates all three?

 

[snowblizz]

Also, does anyone know how to link these so they are either one macro or so the command button activates all three?

This would run the code you posted in sequence. Though you do actions on several different worksheets? Also the first lines, I think it hides unhidden columns and if they are hidden unhides them? So each time you run the code it will either hide/unhide/hide/etc etc etc

Sub ViewData_Click()
 'Unhide cells
    Sheet3.Columns("H:J").EntireColumn.Hidden = _
    Not Sheet3.Columns("H:J").EntireColumn.Hidden

    ' Clear cells in a range
    Sheets("Sheet1").Range("J10:L56").ClearContents

    call DatedSaveData2
End Sub

trying to beat out the bug from the other macro.
</pre>

 

[Bushman]

Nice one! I hadn't thought about the hide/unhide macro....
Thanks for all your efforts. VBA, if only I could speak the language!

 

[snowblizz]

Finally managed to find the error I was making, damned Excel when I use a known variable just not the RIGHT one and it doesn't tell me. Aaargh.

Just a quicky, the first and second times it runs, would this look how you expect it? keep in mind "sd" and "kd" are just a couple of dummy variables.

Excel Workbook
	A	B
1	3.6.2010 17:42	3.6.2010 17:42
2	sd	sd1
3	sd	sd1
4	sd	sd1
5	ks	ks1
6	ks	ks1
7	ks	ks1
8	ks	ks1
Saved

Excel 2003

Cell Formulas
Range	Formula
A1	=NOW()
B1	=NOW()

 

[Bushman]

Looks perfect! How will you do that?