VBA to count fill cell

jmazorra

Well-known Member
Joined
Mar 19, 2011
Messages
715
Hello:

I am looking for a VBA that will count the number of filled cells in column A starting from row A2 (A1 has headers) down and then insert the same amount of rows in sheet 1, sheet 2 and sheet 3 starting at row A7 for all 3.

Any help will be appreciated.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
So are you saying you want every row with something in Column "A" to be copied to Sheets 1,2 and 3 at row 7 ?
What is the sheet that has this data now ?
 
Upvote 0
The sheet is called Status Report. What I need is to count in Status Report Column A every row from A2 down that contains data. Then in Sheet 1, Sheet 2 and Sheet 3 insert that same amount of rows starting from row A7 and down.
 
Upvote 0
Code:
Sub Count_And_Insert()
    
    Dim c As Long, ws As Worksheet
    
    c = Application.WorksheetFunction.CountA(Sheets("Status Report").Range("A:A")) - 1
    
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        ws.Rows(7).Resize(c).Insert
    Next ws
    
End Sub
 
Upvote 0
Code:
Sub Count_And_Insert()
 
    Dim c As Long, ws As Worksheet
 
    c = Application.WorksheetFunction.CountA(Sheets("Status Report").Range("A:A")) - 1
 
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        ws.Rows(7).Resize(c).Insert
    Next ws
 
End Sub

One last thing, I tried writing a macro to copy Status Report A1 down by selecting the column and copy on Sheet 1, Sheet 2 and Sheet 3 on A7 down, but Excel would not allow the paste becuase is not the same size.

Any ideas on VBA?

Thanks in advance
 
Upvote 0
Too vague. Need to provide more details.

You want to copy rows 2 to Last-used-row on sheet Status Report to
row 7 on sheets("Sheet1", "Sheet2", "Sheet3")? Is that correct?
 
Upvote 0
Code:
Sub Copy_Status_Report()
    
    Dim rng As Range, ws As Worksheet
    
    Application.ScreenUpdating = False
    
    With Sheets("Status Report")
        Set rng = .Rows("2:" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With
    
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        rng.Copy
        ws.Rows(7).Insert Shift:=xlDown
    Next ws
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
That worked perfectly, one last problem I have. It is similar to the one worked on for me with a little twist.

For sake of keeping it simple I will keep the same sheet names I can always change later.

Can I copy column A to last used in status report and then paste that on the other 3 sheets on row A7 and down that column? I just need the information from column A in status report, not the whole sheet.

I am pretty sure all I have to do is change a couple of words in the code, but it did not work for me.

I believe this should be the end for my project.

Thanks for the help
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

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