How to link command button macros

Bushman

New Member
Joined
May 23, 2010
Messages
24
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? :confused:
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! :eek:

Code:
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
Made a few fixes, see if this works for you.
Code:
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

New Member
Joined
May 23, 2010
Messages
24
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

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
Oh, columns eh. Try this, hopefully works.

Code:
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

New Member
Joined
May 23, 2010
Messages
24

ADVERTISEMENT

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....
Code:
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

New Member
Joined
May 23, 2010
Messages
24
Also, does anyone know how to link these so they are either one macro or so the command button activates all three?
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123

ADVERTISEMENT

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
Code:
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

New Member
Joined
May 23, 2010
Messages
24
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

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
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
AB
13.6.2010 17:423.6.2010 17:42
2sdsd1
3sdsd1
4sdsd1
5ksks1
6ksks1
7ksks1
8ksks1
Saved
Excel 2003
Cell Formulas
RangeFormula
A1=NOW()
B1=NOW()
 

Forum statistics

Threads
1,148,393
Messages
5,746,441
Members
424,019
Latest member
dpteo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top