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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

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