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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
182
NdNoviceHlp
NdNoviceHlp
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
866
Cubist
Cubist
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
78
Ali M
A
S
  • Solved
copy data from userform to multiple sheets based on checkbox selection
Replies
4
Views
138
Starchaser
S

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

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