Copy data into different named multiple sheets
2
Dears,
I am a beginner and tried to prepare the macro that enables firstly delete rows based on condition, than create new sheets based on criteria from the first main sheet and add data from the first main sheet into multiple named sheets.
- deletes rows based on condition (RUNs OK)
- creates new sheets based on criteria from the first main sheet (RUNs OK)
- adds data from the first main sheet (constant range I4:I6)
into multiple named sheets to A1:A3 in all of them (being created by this macro). Unfortunately I do not know how to do that :-(
Could you possibly help me, please?
Private Sub CommandButton1_Click()
Dim lastrow As Long, x As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 1 Step -1
If UCase(Cells(x, 3).Value) = "0" And _
UCase(Cells(x, 6).Value) = "0" Then
Rows(x).Delete
End If
Next
lastcell = ThisWorkbook.Worksheets("Obratova predvaha").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastcell
With ThisWorkbook
newname = ThisWorkbook.Worksheets("Obratova predvaha").Cells(i, 1).Value
.Sheets.Add after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = newname
End With
Next
ThisWorkbook.Worksheets("Obratova predvaha").Activate
ThisWorkbook.Worksheets("Obratova predvaha").Cells(1, 1).Select
End Sub
excel vba
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
asked Nov 25 '18 at 13:30
MilanMilan
132
add a comment |
2
Dears,
I am a beginner and tried to prepare the macro that enables firstly delete rows based on condition, than create new sheets based on criteria from the first main sheet and add data from the first main sheet into multiple named sheets.
- deletes rows based on condition (RUNs OK)
- creates new sheets based on criteria from the first main sheet (RUNs OK)
- adds data from the first main sheet (constant range I4:I6)
into multiple named sheets to A1:A3 in all of them (being created by this macro). Unfortunately I do not know how to do that :-(
Could you possibly help me, please?
Private Sub CommandButton1_Click()
Dim lastrow As Long, x As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 1 Step -1
If UCase(Cells(x, 3).Value) = "0" And _
UCase(Cells(x, 6).Value) = "0" Then
Rows(x).Delete
End If
Next
lastcell = ThisWorkbook.Worksheets("Obratova predvaha").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastcell
With ThisWorkbook
newname = ThisWorkbook.Worksheets("Obratova predvaha").Cells(i, 1).Value
.Sheets.Add after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = newname
End With
Next
ThisWorkbook.Worksheets("Obratova predvaha").Activate
ThisWorkbook.Worksheets("Obratova predvaha").Cells(1, 1).Select
End Sub
excel vba
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
asked Nov 25 '18 at 13:30
MilanMilan
132
add a comment |
2
2
2
Dears,
I am a beginner and tried to prepare the macro that enables firstly delete rows based on condition, than create new sheets based on criteria from the first main sheet and add data from the first main sheet into multiple named sheets.
- deletes rows based on condition (RUNs OK)
- creates new sheets based on criteria from the first main sheet (RUNs OK)
- adds data from the first main sheet (constant range I4:I6)
into multiple named sheets to A1:A3 in all of them (being created by this macro). Unfortunately I do not know how to do that :-(
Could you possibly help me, please?
Private Sub CommandButton1_Click()
Dim lastrow As Long, x As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 1 Step -1
If UCase(Cells(x, 3).Value) = "0" And _
UCase(Cells(x, 6).Value) = "0" Then
Rows(x).Delete
End If
Next
lastcell = ThisWorkbook.Worksheets("Obratova predvaha").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastcell
With ThisWorkbook
newname = ThisWorkbook.Worksheets("Obratova predvaha").Cells(i, 1).Value
.Sheets.Add after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = newname
End With
Next
ThisWorkbook.Worksheets("Obratova predvaha").Activate
ThisWorkbook.Worksheets("Obratova predvaha").Cells(1, 1).Select
End Sub
excel vba
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
asked Nov 25 '18 at 13:30
MilanMilan
132
Dears,
I am a beginner and tried to prepare the macro that enables firstly delete rows based on condition, than create new sheets based on criteria from the first main sheet and add data from the first main sheet into multiple named sheets.
- deletes rows based on condition (RUNs OK)
- creates new sheets based on criteria from the first main sheet (RUNs OK)
- adds data from the first main sheet (constant range I4:I6)
into multiple named sheets to A1:A3 in all of them (being created by this macro). Unfortunately I do not know how to do that :-(
Could you possibly help me, please?
Private Sub CommandButton1_Click()
Dim lastrow As Long, x As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 1 Step -1
If UCase(Cells(x, 3).Value) = "0" And _
UCase(Cells(x, 6).Value) = "0" Then
Rows(x).Delete
End If
Next
lastcell = ThisWorkbook.Worksheets("Obratova predvaha").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastcell
With ThisWorkbook
newname = ThisWorkbook.Worksheets("Obratova predvaha").Cells(i, 1).Value
.Sheets.Add after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = newname
End With
Next
ThisWorkbook.Worksheets("Obratova predvaha").Activate
ThisWorkbook.Worksheets("Obratova predvaha").Cells(1, 1).Select
End Sub
excel vba
excel vba
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
asked Nov 25 '18 at 13:30
MilanMilan
132
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
asked Nov 25 '18 at 13:30
MilanMilan
132
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
edited Nov 25 '18 at 14:24
Pragmateek
9,23185488
9,23185488
asked Nov 25 '18 at 13:30
MilanMilan
132
asked Nov 25 '18 at 13:30
MilanMilan
132
asked Nov 25 '18 at 13:30
MilanMilan
132
132
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
not very sure about your description, but you may try this:
edited to add a sheet variable and prevent any (possible?) time lapse misbehavior between new sheet adding and writing to it by implicitly assuming it as ActiveSheet:
Option Explicit
Private Sub CommandButton1_Click()
Dim lastrow As Long, i As Long
Dim newSheet As Worksheet
With Worksheets("Obratova predvaha")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If UCase(.Cells(i, 3).Value) = "0" And UCase(.Cells(i, 6).Value) = "0" Then .Rows(i).Delete
Next
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Set newSheet = Sheets.Add(after:=Sheets(Sheets.Count)) ' add a new sheet and hold its reference in newSheet variable
newSheet.Range("A1:A3").Value = .Range("I4:I6").Value ' copy referenced sheet I4:I6 values into newly added sheet cells A1:A3
newSheet.Name = .Cells(i, 1).Value ' change the name of newly added sheet
Next
End With
End Sub
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
If you need to cancel them just add.Range("I4:I6").ClearContentsright beforeEnd With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you
– DisplayName
Nov 25 '18 at 14:53
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
|
show 9 more comments
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%2f53467982%2fcopy-data-into-different-named-multiple-sheets%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
not very sure about your description, but you may try this:
edited to add a sheet variable and prevent any (possible?) time lapse misbehavior between new sheet adding and writing to it by implicitly assuming it as ActiveSheet:
Option Explicit
Private Sub CommandButton1_Click()
Dim lastrow As Long, i As Long
Dim newSheet As Worksheet
With Worksheets("Obratova predvaha")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If UCase(.Cells(i, 3).Value) = "0" And UCase(.Cells(i, 6).Value) = "0" Then .Rows(i).Delete
Next
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Set newSheet = Sheets.Add(after:=Sheets(Sheets.Count)) ' add a new sheet and hold its reference in newSheet variable
newSheet.Range("A1:A3").Value = .Range("I4:I6").Value ' copy referenced sheet I4:I6 values into newly added sheet cells A1:A3
newSheet.Name = .Cells(i, 1).Value ' change the name of newly added sheet
Next
End With
End Sub
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
If you need to cancel them just add.Range("I4:I6").ClearContentsright beforeEnd With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you
– DisplayName
Nov 25 '18 at 14:53
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
|
show 9 more comments
1
not very sure about your description, but you may try this:
edited to add a sheet variable and prevent any (possible?) time lapse misbehavior between new sheet adding and writing to it by implicitly assuming it as ActiveSheet:
Option Explicit
Private Sub CommandButton1_Click()
Dim lastrow As Long, i As Long
Dim newSheet As Worksheet
With Worksheets("Obratova predvaha")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If UCase(.Cells(i, 3).Value) = "0" And UCase(.Cells(i, 6).Value) = "0" Then .Rows(i).Delete
Next
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Set newSheet = Sheets.Add(after:=Sheets(Sheets.Count)) ' add a new sheet and hold its reference in newSheet variable
newSheet.Range("A1:A3").Value = .Range("I4:I6").Value ' copy referenced sheet I4:I6 values into newly added sheet cells A1:A3
newSheet.Name = .Cells(i, 1).Value ' change the name of newly added sheet
Next
End With
End Sub
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
If you need to cancel them just add.Range("I4:I6").ClearContentsright beforeEnd With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you
– DisplayName
Nov 25 '18 at 14:53
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
|
show 9 more comments
1
1
1
not very sure about your description, but you may try this:
edited to add a sheet variable and prevent any (possible?) time lapse misbehavior between new sheet adding and writing to it by implicitly assuming it as ActiveSheet:
Option Explicit
Private Sub CommandButton1_Click()
Dim lastrow As Long, i As Long
Dim newSheet As Worksheet
With Worksheets("Obratova predvaha")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If UCase(.Cells(i, 3).Value) = "0" And UCase(.Cells(i, 6).Value) = "0" Then .Rows(i).Delete
Next
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Set newSheet = Sheets.Add(after:=Sheets(Sheets.Count)) ' add a new sheet and hold its reference in newSheet variable
newSheet.Range("A1:A3").Value = .Range("I4:I6").Value ' copy referenced sheet I4:I6 values into newly added sheet cells A1:A3
newSheet.Name = .Cells(i, 1).Value ' change the name of newly added sheet
Next
End With
End Sub
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
not very sure about your description, but you may try this:
edited to add a sheet variable and prevent any (possible?) time lapse misbehavior between new sheet adding and writing to it by implicitly assuming it as ActiveSheet:
Option Explicit
Private Sub CommandButton1_Click()
Dim lastrow As Long, i As Long
Dim newSheet As Worksheet
With Worksheets("Obratova predvaha")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = lastrow To 1 Step -1
If UCase(.Cells(i, 3).Value) = "0" And UCase(.Cells(i, 6).Value) = "0" Then .Rows(i).Delete
Next
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Set newSheet = Sheets.Add(after:=Sheets(Sheets.Count)) ' add a new sheet and hold its reference in newSheet variable
newSheet.Range("A1:A3").Value = .Range("I4:I6").Value ' copy referenced sheet I4:I6 values into newly added sheet cells A1:A3
newSheet.Name = .Cells(i, 1).Value ' change the name of newly added sheet
Next
End With
End Sub
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
edited Nov 25 '18 at 16:27
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
answered Nov 25 '18 at 14:06
DisplayNameDisplayName
10.6k2519
10.6k2519
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
If you need to cancel them just add.Range("I4:I6").ClearContentsright beforeEnd With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you
– DisplayName
Nov 25 '18 at 14:53
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
|
show 9 more comments
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
If you need to cancel them just add.Range("I4:I6").ClearContentsright beforeEnd With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you
– DisplayName
Nov 25 '18 at 14:53
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
Thanx for this. Unfortunately it does not run properly (macro fill in the data even into the "main sheet". I have just needed to copy the data from the main sheet (name "Obratova predvaha") into all other sheets besides "Obratova predvaha"
– Milan
Nov 25 '18 at 14:32
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
data are in the "main" sheet (i.e. "Obratova predvaha" sheet) already, otherwise how could you copy their value from there to other sheets?
– DisplayName
Nov 25 '18 at 14:39
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
sorry...the data I need to copy already exists in the main sheet
– Milan
Nov 25 '18 at 14:51
If you need to cancel them just add
.Range("I4:I6").ClearContents right before End With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you– DisplayName
Nov 25 '18 at 14:53
If you need to cancel them just add
.Range("I4:I6").ClearContents right before End With. And if my answer solved your question then you may consider marking it as accepted, too. Thank you– DisplayName
Nov 25 '18 at 14:53
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
Still doesn´t work properly...when I use Range("A1:A3").value = .Range("I4:I6").value, macro copy the data even into the main sheet "Obratova predvaha" as well which is unwanted, I just need to copy the data from the main sheet ("Obratova predvaha") into the others (2,3....xxx = but named differently, not just Sheet2,Sheet3 etc).
– Milan
Nov 25 '18 at 15:10
|
show 9 more comments
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%2f53467982%2fcopy-data-into-different-named-multiple-sheets%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
