Copy the data from Master data to Multiple sheets based on the condition for Sep 1 to Sep 30.

S

seenuguddu

New Member
Joined
Oct 19, 2018
Messages
2
Hi Gurus


i am newbie in VBA and i have tried a lot by searching in google to create a script and finally end up here for help.
need a help on this to copy the data from Master data to Multiple sheets based on the condition in excel for Sep 1 to Sep 30.
copying the values based on the Rownum Column name from Master data.i have a Master sheet with the data and need to generate the report for every Month.
Creating multiple sheets based on the condition Rownum = 1 for Sep 1 and so on.

Code: 
Ex:-

Rownum = 1  for Sep 1
Rownum = 2  for Sep 2
Rownum = 3  for Sep 3
Rownum = 4  for Sep 4
Rownum = 5  for Sep 5
.
.
Rownum = 29  for Sep 29
Rownum = 30  for Sep 30

i need to create a multiple sheet based on the condition until Sep 30 is done from Master Sheet

Code: 
Option Explicit

Sub AddSheets()
    Dim siteCount As Integer
    Dim i As Integer
    Dim site_i As Worksheet
  
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MasterSheet")


    Dim r As Long, endRow as Long, pasteRowIndex As Long
    ' endRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    siteCount = 3


    For i = 1 To siteCount
        Set site_i = Sheets.Add(after:=Sheets(Worksheets.Count))
        site_i.Name = "Sep " & CStr(i)
    Next i
    Sheets.FillAcrossSheets ws.Range("1:1")
    
    Sheets("MasterSheet").Select
    endRow = Cells(Rows.Count, "C").End(xlUp).Row
    pasteRowIndex = 2
    For r = 2 To endRow
     
        If Cells(r, Columns("C").Column).Value = 1 Then

            Rows(r).Select
            Selection.Copy

            Sheets("Sep 1").Select
            Rows(pasteRowIndex).Select
            ActiveSheet.Paste

            pasteRowIndex = pasteRowIndex + 1
            Sheets("MasterSheet").Select 
        End If
    Next r

End Sub

Thanks in Advance.

Code: 
[TABLE="width: 300"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]RowNums[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018 9:45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018 10:35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 6:15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 9:45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 10:35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 4:45[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 5:35[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 6:15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 9:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 11:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 5:35[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 6:15[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 9:15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]9/4/2018 6:25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
forgot to send the sample data. here is the sample data
Code: 
-- Sample Code
[TABLE="width: 300"]
<tbody>[TR]
[TD]MasterSheet[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]RowNums[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018 9:45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018 10:35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 6:15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 9:45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 10:35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 4:45[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 5:35[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 6:15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 9:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 11:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 5:35[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 6:15[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 9:15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]9/4/2018 6:25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

-Output
Code: 
[TABLE="width: 292"]
<tbody>[TR]
[TD]SheetName[/TD]
[TD="align: right"]1-Sep[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]RowNums[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018 9:45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2018 10:35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 6:15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]SheetName[/TD]
[TD="align: right"]2-Sep[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]RowNums[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 9:45[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/1/2018 10:35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 4:45[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 5:35[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 6:15[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]SheetName[/TD]
[TD="align: right"]3-Sep[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]RowNums[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 9:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/2/2018 11:15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 5:35[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 6:15[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]SheetName[/TD]
[TD="align: right"]4-Sep[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[TD]RowNums[/TD]
[/TR]
[TR]
[TD="align: right"]9/3/2018 9:15[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]9/4/2018 6:25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
That might be a pivot table solution taking into consideration that formulas of "=month(...)' and "=day(...)" yields the number value of each . By entering those formulas in each of two adjacent columns, all the control you need to create the output is available and can be processed via a pivot table.
 
Upvote 0
You must log in or register to reply here.

Similar threads

netspeedz
  • Solved
Extracting Data From Kitco.com [Using VBA]
Replies
6
Views
417
netspeedz
netspeedz
L
  • Question
INDEX MATCH
Replies
1
Views
175
Fluff
Fluff
K
  • Question
Average based on importance level from 1-9
Replies
5
Views
138
Abdmujib
A
I
  • Solved
(code adjustment needed) Importing data from multiple .csv files to individual column entries
Replies
2
Views
468
Itshouldbeonebutton
I
ExcelNewbie2020
  • Solved
transpose data
2
Replies
15
Views
179
kvsrinivasamurthy
K

Forum statistics

Threads
1,214,787
Messages
6,121,569
Members
449,038
Latest member
Guest1337

Share this page

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
Back
Top