Macro - Command Button Issue

[Glasgowsmile]

Hello!

I've got the following code to copy two columns on the front page and then paste it horizontally (copied vertically) on another page and then assign the date. Just did this through Record Macro.

Works fine with normal buttons but I'd prefer a Command Button -- but it wont work, what's going on here?

Additionally, there is one other issue. I need to copy/paste this data each day but I don't want it to overwrite the previous data, how do I get it to notice data from yesterday and move down to paste the data below it each time?

Sub Macro8()    Range("B2:B14").Select
    Selection.Copy
    Sheets("LogFile").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Sheet1").Select
    Range("G2:G14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("LogFile").Select
    Range("A2:M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3:M14").Select
    Range("M3").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("O7").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("O8").Select
End Sub

 

[My Aswer Is This]

There are several lines of code which I do not understand why you need like these line:

[LEFT][COLOR=#333333][FONT=monospace]Range("A3:M14").Select
    Range("M3").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("O7").Select
[/FONT][/COLOR][/LEFT]

And why put Todays Date in O7

Will it always be 07 ?

And to run the script in a ActiveX Command Button try this:

Put this line of code in your ActiveX Button

Private Sub CommandButton1_Click()
Call Macro8
End Sub

 

[Glasgowsmile]

Good Morning,

I see - that will fix the command button issue but what about the offset issue?

I'm sure the extra lines are due to using the Record Macro feature instead of typing it out myself.

I've changed the code a bit since the original post, the selection and clear content issue was due to transpose not working with the formula so I updated the formula and that cleared it up.

How would I go about making sure when I save that data once per day, that it goes to the next line below and saves the information and then puts the data in the A column but again, next row down from the previous?

Sub Macro8()    Range("B2:B14").Select
    Selection.Copy
    Sheets("LogFile").Select
    Range("B1").Select
    Selection.PasteSpecial xlPasteValues, Transpose:=True
    Sheets("Sheet1").Select
    Range("G2:G14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("LogFile").Select
    Range("B2").Select
    Selection.PasteSpecial xlPasteValues, Transpose:=True
    Range("A2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A3").Select
End Sub

 

[My Aswer Is This]

Try this:

Sub Macro8()
'Modified  6/1/2019  1:22:55 PM  EDT
Dim Lastrow As Long
Lastrow = Sheets("LogFile").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet1").Range("B2:B14").Copy
Sheets("LogFile").Cells(Lastrow, "B").PasteSpecial xlPasteValues, Transpose:=True
Sheets("Sheet1").Range("G2:G14").Copy
Sheets("LogFile").Cells(Lastrow + 1, "B").PasteSpecial xlPasteValues, Transpose:=True
Sheets("LogFile").Cells(Lastrow, "A").Value = Date
Sheets("LogFile").Cells(Lastrow + 1, "A").Value = Date
Application.CutCopyMode = False
    
End Sub

 

[Glasgowsmile]

Thanks!

I discovered an issue I didn't consider.

I only need to copy/paste and move down the =today() formula and the G2:G20 column on Sheet1 -- B2:B20 can be copied but should be stored at B1:N1 each time.

What do I need to adjust for that?

 

[Glasgowsmile]

I made this adjustment it seems to have worked!

Dim Lastrow As Long
Lastrow = Sheets("LogFile").Cells(Rows.Count, "A").End(xlUp).Row + 1
Sheets("Sheet1").Range("B2:B14").Copy
Sheets("LogFile").Range("B1").PasteSpecial xlPasteValues, Transpose:=True
Sheets("Sheet1").Range("G2:G14").Copy
Sheets("LogFile").Cells(Lastrow, "B").PasteSpecial xlPasteValues, Transpose:=True
Sheets("LogFile").Cells(Lastrow, "A").Value = Date
Sheets("LogFile").Cells(Lastrow, "A").Value = Date
Application.CutCopyMode = False

 

[My Aswer Is This]

If that works for you that's great.

You can see from my code I do not need to select anything

I just copy and paste using no selecting

A lot of users use a lot of selecting and activating.

As you learn more you can see that is not necessary.

[LEFT][COLOR=#222222][FONT=Verdana]Sheets("New").Range("G1").value="Alpha"[/FONT][/COLOR]

[COLOR=#222222][FONT=Verdana]Sheets("New").Range("R21").Copy Sheets("People").Range("K25")[/FONT][/COLOR][/LEFT]