VBA looping through empty cells and copying to new sheet

SilvEn

New Member
Joined
Sep 3, 2014
Messages
2
Hi, i'm having a great deal of trouble with this. I searched everywhere and found smiliar codes but nothing of the exact and unfortunately, my vba skills is not at the level to modified any codes. Anyway.

Below is a portion of how my data looks like. Ther is a set of data for each month all the way down to December. However, after each month of data come an empty cell. Because the number of total rows in each month may change, I can't just explicitly refer the cells. but the amount of empty cells remain constant which is 12

nv4oro.png


starting from A4, I need to find the first empty cell in Column(A) then copy the month of data below it to new sheet. then find the next empty cell, copy next month of data to new sheet and loop all the way until 12th empty cell or December data has been copied. so in this case, i need to first copy rows 4:14, then 15:25, then 26:36, ect

Any help would be greatly appreciated.

Thanks
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Assuming the posted sheet is sheet 1 of the source workbook. If not, modify where Edit is indicated below.
Code:
Sub makeMoSheets()
Dim sh As Worksheet, i As Long, lr As Long
Set sh = Sheets(1) 'Edit Sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 4 To lr Step 11
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(sh.Cells(i + 2, 2).Value, "mm-dd-yyyy")
        sh.Cells(i, 1).Resize(11, 1).EntireRow.Copy ActiveSheet.Range("A1")
    Next
ThisWorkbook.Save
End Sub
 

SilvEn

New Member
Joined
Sep 3, 2014
Messages
2
hey thanks for the reply. i'm sorry for the unclear-ness. i was looking for 1 new sheet. and copy paste each month at the end of the previous month.

for example, if month 1 starts at column (A) and ends at column (AF) then month 2 starts at column (AG) on the same row. i'm basically trying to align all 365/366 days and its data on the same rows.

please help. and thanks.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,530
Messages
5,837,907
Members
430,520
Latest member
VenkateshRajaganesan

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
Top