VBA to count fill cell

[jmazorra]

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.

 

[Michael M]

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 ?

 

[jmazorra]

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.

 

[AlphaFrog]

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

 

[jmazorra]

worked like a charm, I have been breaking my head for the last 3 days.

Thanks

 

[jmazorra]

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

 

[AlphaFrog]

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?

 

[jmazorra]

Sorry about the vagueness, I am new to this. But captured it just right.

 

[AlphaFrog]

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

 

[jmazorra]

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