Macro will work in developer and with hot key but not with button… any idea why?

-2

As mentioned above, the code works just fine both in the developer and with the hot keys but when I use the button, it seems like the "go to last row + 1" doesn't work. When I select the button, the data keeps being copied and recopied into row 2.

Sub Entry_Schedule()

' Entry_Schedule Macro

    Dim FrstEmptCll As Range

    FrstEmtCll = Range("A" & Rows.Count).End(xlUp).Row

    Sheets("Sheet2").Range("D4:I4").Copy

    Sheets("Data").Range("A" & FrstEmtCll + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

   End Sub

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

asked Nov 23 '18 at 6:54

B-Rye

306

Can you explain exactly what you mean by button? Is it a button on a userform, an ActiveX command button, a form control button? Then also, what exactly have to tried with the button itself? We probably need to see the code for the button rather than this.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:57

And "...it seems like the 'go to last row + 1' doesn't work" isn't listed anywhere in your provided code.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:59

Form Control. I don't have a code for the button, I just linked it to the code mentioned above. Sheets("Data").Range("A" & FrstEmtCll + 1).... is what I was referencing with the "go to last row + 1"

– B-Rye
Nov 23 '18 at 6:59

1

So, I am assuming that this "button" is on another sheet, right? Because Range("A" & Rows.Count).End(xlUp).Row isn't qualified with a worksheet. You did good and qualified the next two range objects with sheets Sheet2 and Data, but didn't qualify the range you are having issues with.

– K.Dᴀᴠɪs
Nov 23 '18 at 7:05

1

@Pᴇʜ I have retracted my flag, but can't do anything about the one who flagged before I did (No MCVE I believe it was).

– K.Dᴀᴠɪs
Nov 23 '18 at 7:38

|
show 5 more comments

-2

Sub Entry_Schedule()

' Entry_Schedule Macro

    Dim FrstEmptCll As Range

    FrstEmtCll = Range("A" & Rows.Count).End(xlUp).Row

    Sheets("Sheet2").Range("D4:I4").Copy

    Sheets("Data").Range("A" & FrstEmtCll + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

   End Sub

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

asked Nov 23 '18 at 6:54

B-Rye

306

Can you explain exactly what you mean by button? Is it a button on a userform, an ActiveX command button, a form control button? Then also, what exactly have to tried with the button itself? We probably need to see the code for the button rather than this.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:57

And "...it seems like the 'go to last row + 1' doesn't work" isn't listed anywhere in your provided code.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:59

Form Control. I don't have a code for the button, I just linked it to the code mentioned above. Sheets("Data").Range("A" & FrstEmtCll + 1).... is what I was referencing with the "go to last row + 1"

– B-Rye
Nov 23 '18 at 6:59

1

So, I am assuming that this "button" is on another sheet, right? Because Range("A" & Rows.Count).End(xlUp).Row isn't qualified with a worksheet. You did good and qualified the next two range objects with sheets Sheet2 and Data, but didn't qualify the range you are having issues with.

– K.Dᴀᴠɪs
Nov 23 '18 at 7:05

1

@Pᴇʜ I have retracted my flag, but can't do anything about the one who flagged before I did (No MCVE I believe it was).

– K.Dᴀᴠɪs
Nov 23 '18 at 7:38

|
show 5 more comments

-2

Sub Entry_Schedule()

' Entry_Schedule Macro

    Dim FrstEmptCll As Range

    FrstEmtCll = Range("A" & Rows.Count).End(xlUp).Row

    Sheets("Sheet2").Range("D4:I4").Copy

    Sheets("Data").Range("A" & FrstEmtCll + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

   End Sub

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

asked Nov 23 '18 at 6:54

B-Rye

306

Sub Entry_Schedule()

' Entry_Schedule Macro

    Dim FrstEmptCll As Range

    FrstEmtCll = Range("A" & Rows.Count).End(xlUp).Row

    Sheets("Sheet2").Range("D4:I4").Copy

    Sheets("Data").Range("A" & FrstEmtCll + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

   End Sub

excel vba excel-vba

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

asked Nov 23 '18 at 6:54

B-Rye

306

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

asked Nov 23 '18 at 6:54

B-Rye

306

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

edited Nov 23 '18 at 7:11

Pᴇʜ

24.4k63052

asked Nov 23 '18 at 6:54

B-Rye

306

asked Nov 23 '18 at 6:54

B-Rye

306

asked Nov 23 '18 at 6:54

B-Rye

306

Can you explain exactly what you mean by button? Is it a button on a userform, an ActiveX command button, a form control button? Then also, what exactly have to tried with the button itself? We probably need to see the code for the button rather than this.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:57

And "...it seems like the 'go to last row + 1' doesn't work" isn't listed anywhere in your provided code.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:59

Form Control. I don't have a code for the button, I just linked it to the code mentioned above. Sheets("Data").Range("A" & FrstEmtCll + 1).... is what I was referencing with the "go to last row + 1"

– B-Rye
Nov 23 '18 at 6:59

1

So, I am assuming that this "button" is on another sheet, right? Because Range("A" & Rows.Count).End(xlUp).Row isn't qualified with a worksheet. You did good and qualified the next two range objects with sheets Sheet2 and Data, but didn't qualify the range you are having issues with.

– K.Dᴀᴠɪs
Nov 23 '18 at 7:05

1

@Pᴇʜ I have retracted my flag, but can't do anything about the one who flagged before I did (No MCVE I believe it was).

– K.Dᴀᴠɪs
Nov 23 '18 at 7:38

|
show 5 more comments

Can you explain exactly what you mean by button? Is it a button on a userform, an ActiveX command button, a form control button? Then also, what exactly have to tried with the button itself? We probably need to see the code for the button rather than this.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:57

And "...it seems like the 'go to last row + 1' doesn't work" isn't listed anywhere in your provided code.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:59

Form Control. I don't have a code for the button, I just linked it to the code mentioned above. Sheets("Data").Range("A" & FrstEmtCll + 1).... is what I was referencing with the "go to last row + 1"

– B-Rye
Nov 23 '18 at 6:59

1

So, I am assuming that this "button" is on another sheet, right? Because Range("A" & Rows.Count).End(xlUp).Row isn't qualified with a worksheet. You did good and qualified the next two range objects with sheets Sheet2 and Data, but didn't qualify the range you are having issues with.

– K.Dᴀᴠɪs
Nov 23 '18 at 7:05

1

@Pᴇʜ I have retracted my flag, but can't do anything about the one who flagged before I did (No MCVE I believe it was).

– K.Dᴀᴠɪs
Nov 23 '18 at 7:38

Can you explain exactly what you mean by button? Is it a button on a userform, an ActiveX command button, a form control button? Then also, what exactly have to tried with the button itself? We probably need to see the code for the button rather than this.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:57

And "...it seems like the 'go to last row + 1' doesn't work" isn't listed anywhere in your provided code.

– K.Dᴀᴠɪs
Nov 23 '18 at 6:59

Form Control. I don't have a code for the button, I just linked it to the code mentioned above. Sheets("Data").Range("A" & FrstEmtCll + 1).... is what I was referencing with the "go to last row + 1"

– B-Rye
Nov 23 '18 at 6:59

So, I am assuming that this "button" is on another sheet, right? Because Range("A" & Rows.Count).End(xlUp).Row isn't qualified with a worksheet. You did good and qualified the next two range objects with sheets Sheet2 and Data, but didn't qualify the range you are having issues with.

– K.Dᴀᴠɪs
Nov 23 '18 at 7:05

@Pᴇʜ I have retracted my flag, but can't do anything about the one who flagged before I did (No MCVE I believe it was).

– K.Dᴀᴠɪs
Nov 23 '18 at 7:38

|
show 5 more comments

1 Answer
1

active

oldest

votes

You need to qualify every Range, Rows, Columns, etc with a worksheet:

FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row

The second issue is that you declare Dim FrstEmptCll As Range but you try to put the row number into a range object .End(xlUp).Row.

So either Set the range to the cell and offset:

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmtCll As Range

    Set FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Offset(RowOffset:=1)

    Worksheets("Sheet2").Range("D4:I4").Copy

    FrstEmtCll.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Or use the row number (as Long):

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmptRow As Long

    FrstEmptRow = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row + 1

    Worksheets("Sheet2").Range("D4:I4").Copy

    Worksheets("Data").Range("A" & FrstEmptRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Additionally you mistyped your variable name FrstEmptCll vs FrstEmtCll (missing p). This means you actually have 2 different variables now (which easily blows up your code). To avoid such errors I highly recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration.

edited Nov 23 '18 at 7:25

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

1

This is very good advice. Thank you. I'm new to VBA so I've got a lot of catching up to do.

– B-Rye
Nov 23 '18 at 7:30

1

@B-Rye Especially if you are new to VBA Option Explicit forces you to write good code and prevents you from running into errors.

– Pᴇʜ
Nov 23 '18 at 7:31

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53441931%2fmacro-will-work-in-developer-and-with-hot-key-but-not-with-button-any-idea-wh%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You need to qualify every Range, Rows, Columns, etc with a worksheet:

FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row

The second issue is that you declare Dim FrstEmptCll As Range but you try to put the row number into a range object .End(xlUp).Row.

So either Set the range to the cell and offset:

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmtCll As Range

    Set FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Offset(RowOffset:=1)

    Worksheets("Sheet2").Range("D4:I4").Copy

    FrstEmtCll.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Or use the row number (as Long):

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmptRow As Long

    FrstEmptRow = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row + 1

    Worksheets("Sheet2").Range("D4:I4").Copy

    Worksheets("Data").Range("A" & FrstEmptRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

edited Nov 23 '18 at 7:25

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

1

This is very good advice. Thank you. I'm new to VBA so I've got a lot of catching up to do.

– B-Rye
Nov 23 '18 at 7:30

1

@B-Rye Especially if you are new to VBA Option Explicit forces you to write good code and prevents you from running into errors.

– Pᴇʜ
Nov 23 '18 at 7:31

add a comment |

You need to qualify every Range, Rows, Columns, etc with a worksheet:

FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row

The second issue is that you declare Dim FrstEmptCll As Range but you try to put the row number into a range object .End(xlUp).Row.

So either Set the range to the cell and offset:

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmtCll As Range

    Set FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Offset(RowOffset:=1)

    Worksheets("Sheet2").Range("D4:I4").Copy

    FrstEmtCll.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Or use the row number (as Long):

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmptRow As Long

    FrstEmptRow = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row + 1

    Worksheets("Sheet2").Range("D4:I4").Copy

    Worksheets("Data").Range("A" & FrstEmptRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

edited Nov 23 '18 at 7:25

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

1

This is very good advice. Thank you. I'm new to VBA so I've got a lot of catching up to do.

– B-Rye
Nov 23 '18 at 7:30

1

@B-Rye Especially if you are new to VBA Option Explicit forces you to write good code and prevents you from running into errors.

– Pᴇʜ
Nov 23 '18 at 7:31

add a comment |

You need to qualify every Range, Rows, Columns, etc with a worksheet:

FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row

The second issue is that you declare Dim FrstEmptCll As Range but you try to put the row number into a range object .End(xlUp).Row.

So either Set the range to the cell and offset:

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmtCll As Range

    Set FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Offset(RowOffset:=1)

    Worksheets("Sheet2").Range("D4:I4").Copy

    FrstEmtCll.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Or use the row number (as Long):

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmptRow As Long

    FrstEmptRow = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row + 1

    Worksheets("Sheet2").Range("D4:I4").Copy

    Worksheets("Data").Range("A" & FrstEmptRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

edited Nov 23 '18 at 7:25

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

You need to qualify every Range, Rows, Columns, etc with a worksheet:

FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row

The second issue is that you declare Dim FrstEmptCll As Range but you try to put the row number into a range object .End(xlUp).Row.

So either Set the range to the cell and offset:

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmtCll As Range

    Set FrstEmtCll = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Offset(RowOffset:=1)

    Worksheets("Sheet2").Range("D4:I4").Copy

    FrstEmtCll.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

Or use the row number (as Long):

Option Explicit



Sub Entry_Schedule()

    ' Entry_Schedule Macro

    Dim FrstEmptRow As Long

    FrstEmptRow = Worksheets("Data").Range("A" & Worksheets("Data").Rows.Count).End(xlUp).Row + 1

    Worksheets("Sheet2").Range("D4:I4").Copy

    Worksheets("Data").Range("A" & FrstEmptRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

End Sub

edited Nov 23 '18 at 7:25

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

edited Nov 23 '18 at 7:25

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

answered Nov 23 '18 at 7:15

Pᴇʜ

24.4k63052

1

This is very good advice. Thank you. I'm new to VBA so I've got a lot of catching up to do.

– B-Rye
Nov 23 '18 at 7:30

1

@B-Rye Especially if you are new to VBA Option Explicit forces you to write good code and prevents you from running into errors.

– Pᴇʜ
Nov 23 '18 at 7:31

add a comment |

1

This is very good advice. Thank you. I'm new to VBA so I've got a lot of catching up to do.

– B-Rye
Nov 23 '18 at 7:30

1

@B-Rye Especially if you are new to VBA Option Explicit forces you to write good code and prevents you from running into errors.

– Pᴇʜ
Nov 23 '18 at 7:31

This is very good advice. Thank you. I'm new to VBA so I've got a lot of catching up to do.

– B-Rye
Nov 23 '18 at 7:30

@B-Rye Especially if you are new to VBA Option Explicit forces you to write good code and prevents you from running into errors.

– Pᴇʜ
Nov 23 '18 at 7:31

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr