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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
1K
netspeedz
netspeedz
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
848
Cubist
Cubist
L
  • Solved
Macro to open multiple workbooks and copy a range of data based on todays date. Then paste the data in master workbook in one sheet under each other.
Replies
8
Views
584
Luke1690
L
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
321
Trixterz
T
L
  • Question Question
INDEX MATCH
Replies
1
Views
323
Fluff
Fluff

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

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