Merging two macros
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have a sheet with raw data called "Raw Data" I want to copy this data into another sheet called "Data".
In "Raw Data" I have a Named Range called "RawTab1"
In "Data" I have a Table called "DataTable" where I want to paste the data from range "RawTab1" but not the two first two rows from the range "RawTab1"
A have made this macro to cleare the data in the DataTable before pasting the new data from RawTab1:
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
End Sub
And this is the macro to copy the data from RawTab1 range into the DataTable
Sub CopyRawTab1()
Application.Goto Reference:="RawTab1"
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
So my questions are:
- How can I merge these two macros?
- How can avoide not to paste the two first rows from RawTab1 into the DataTable?
excel vba excel-vba
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
add a comment |
0
I have a sheet with raw data called "Raw Data" I want to copy this data into another sheet called "Data".
In "Raw Data" I have a Named Range called "RawTab1"
In "Data" I have a Table called "DataTable" where I want to paste the data from range "RawTab1" but not the two first two rows from the range "RawTab1"
A have made this macro to cleare the data in the DataTable before pasting the new data from RawTab1:
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
End Sub
And this is the macro to copy the data from RawTab1 range into the DataTable
Sub CopyRawTab1()
Application.Goto Reference:="RawTab1"
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
So my questions are:
- How can I merge these two macros?
- How can avoide not to paste the two first rows from RawTab1 into the DataTable?
excel vba excel-vba
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
1
I recommend to read and apply How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 22 '18 at 14:16
add a comment |
0
0
0
I have a sheet with raw data called "Raw Data" I want to copy this data into another sheet called "Data".
In "Raw Data" I have a Named Range called "RawTab1"
In "Data" I have a Table called "DataTable" where I want to paste the data from range "RawTab1" but not the two first two rows from the range "RawTab1"
A have made this macro to cleare the data in the DataTable before pasting the new data from RawTab1:
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
End Sub
And this is the macro to copy the data from RawTab1 range into the DataTable
Sub CopyRawTab1()
Application.Goto Reference:="RawTab1"
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
So my questions are:
- How can I merge these two macros?
- How can avoide not to paste the two first rows from RawTab1 into the DataTable?
excel vba excel-vba
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
I have a sheet with raw data called "Raw Data" I want to copy this data into another sheet called "Data".
In "Raw Data" I have a Named Range called "RawTab1"
In "Data" I have a Table called "DataTable" where I want to paste the data from range "RawTab1" but not the two first two rows from the range "RawTab1"
A have made this macro to cleare the data in the DataTable before pasting the new data from RawTab1:
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
End Sub
And this is the macro to copy the data from RawTab1 range into the DataTable
Sub CopyRawTab1()
Application.Goto Reference:="RawTab1"
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
So my questions are:
- How can I merge these two macros?
- How can avoide not to paste the two first rows from RawTab1 into the DataTable?
excel vba excel-vba
excel vba excel-vba
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
asked Nov 22 '18 at 14:14
NewCalcNewCalc
32
32
1
I recommend to read and apply How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 22 '18 at 14:16
add a comment |
1
I recommend to read and apply How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 22 '18 at 14:16
1
1
I recommend to read and apply How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 22 '18 at 14:16
I recommend to read and apply How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 22 '18 at 14:16
add a comment |
1 Answer
1
active
oldest
votes
1
It should be something like the following:
Option Explicit
Public Sub Combined()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
With Worksheets("Raw Data").Range("RawTab1")
'copy everything from RawTab1 but not the first 2 rows
.Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
End With
sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1 range before copying it.
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
1
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53432881%2fmerging-two-macros%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
It should be something like the following:
Option Explicit
Public Sub Combined()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
With Worksheets("Raw Data").Range("RawTab1")
'copy everything from RawTab1 but not the first 2 rows
.Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
End With
sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1 range before copying it.
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
1
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
add a comment |
1
It should be something like the following:
Option Explicit
Public Sub Combined()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
With Worksheets("Raw Data").Range("RawTab1")
'copy everything from RawTab1 but not the first 2 rows
.Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
End With
sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1 range before copying it.
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
1
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
add a comment |
1
1
1
It should be something like the following:
Option Explicit
Public Sub Combined()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
With Worksheets("Raw Data").Range("RawTab1")
'copy everything from RawTab1 but not the first 2 rows
.Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
End With
sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1 range before copying it.
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
It should be something like the following:
Option Explicit
Public Sub Combined()
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Data")
sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
With Worksheets("Raw Data").Range("RawTab1")
'copy everything from RawTab1 but not the first 2 rows
.Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
End With
sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1 range before copying it.
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
edited Nov 22 '18 at 14:27
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
answered Nov 22 '18 at 14:21
PᴇʜPᴇʜ
25.3k63052
25.3k63052
1
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
add a comment |
1
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
1
1
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
@Peh, this is exactly what I was looking for, it works great. Thank you!
– NewCalc
Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect.
– NewCalc
Jan 18 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see Minimal, Complete, and Verifiable example. Comments are not sufficient to answer that.
– Pᴇʜ
Jan 18 at 15:38
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
Done, created a [new quastion] (stackoverflow.com/questions/54257589/…)
– NewCalc
Jan 18 at 16:09
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53432881%2fmerging-two-macros%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
I recommend to read and apply How to avoid using Select in Excel VBA
– Pᴇʜ
Nov 22 '18 at 14:16