Modifying my code

[smilelover]

I have successfully done what I need to do, this is my modified VBA code:

Private Sub CommandButton1_Click()
colarow = 1
For colbrow = 1 To 7000 Step 7
Range("=Sheet4!A" & colbrow).Formula = ("=Sheet1!A" & colarow)
colarow = colarow + 1
Next
End Sub

because on Sheet1!A I have a list of stuff and they may increase or decrease any thing, in this point I assume that they won't go over 1000.
I wanted to create a list on Sheet4!A which are identical to Sheet1!A except I have 7 blank rows in between each data and I've achieved that.

Problem 1: How can I hide or delete the 0 entries? For example I have 10 data on Sheet1!A, that means that I would have 70 rows on Sheet4!A which are significant for me, the other 6930 rows of 0's and empties are useless which I hope to get rid, ideas?

Problem 2: I wish the list on Sheet4!A to start every time when I open up the spreadsheet, without the need to click the Command Button, I know it will sync the two rows after I click it once, but I hope to reduce it to automatic, without the need to click the Command Button every time I start up.

You guys are wonderful, keep up the good work!!!

 

[smilelover]

http://s1087.photobucket.com/albums/j467/smileloveangel/

there are the 3 photos in the album,

sorry about that again

 

[RichardMGreen]

I've seen them.
Let me have a think, it shouldn't be too difficult to sort out.

 

[smilelover]

That is very kind of you, thank you!!

 

[smilelover]

Forget to add, my current code:
Private Sub CommandButton1_Click()
colarow = 1
For colbrow = 1 To 7000 Step 7
Range("=Sheet4!A" & colbrow).Formula = ("=Sheet1!A" & colarow)
Range("=Sheet4!L" & colbrow).Formula = ("=Sheet1!B" & colarow)
Range("=Sheet4!M" & colbrow).Formula = ("=Sheet1!C" & colarow)
Range("=Sheet4!N" & colbrow).Formula = ("=Sheet1!D" & colarow)
colarow = colarow + 1
Next
End Sub

 

[moonfish]

Private Sub Workbook_Open()
    dim rowA as Long
    For rowA = 1 To WorksheetFunction.CountA(Range("Sheet1!A:A"))
        Range("=Sheet4!A" & rowA*7).Formula = ("=Sheet1!A" & rowA)
    Next rowA
End Sub

This way it doesn't hide anything, it just generates exactly as many entries as you need (also removes the 1000 cap).

 

[smilelover]

Oh thanks moonfish!!
By the way, I got the workbook open working
but it won't sync to sheet1 when there is a change in Sheet1!A
Say if I add a new data at the end of Sheet1!A, it won't appear on Sheet4!A,
help?

 

[smilelover]

To be more precise, it seems to sync with changes of existing data in Sheet1!A but not new data, help?

 

[moonfish]

You'll need something different for that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("Sheet1!A:A")) Is Nothing Then
        Range("=Sheet4!A" & Target.Row*7).Formula = Target.Formula
    End If
End Sub

Put it below the Workbook_Open() sub.

Not tested because I don't have Excel right now but the concept of it is clear, no?

 

[smilelover]

Hi It is still not working, I have to manually close the Excel again after inserting data in Sheet1!A and reopen it to let it appear on Sheet4!A

 

[moonfish]

Do you get some sort of error message when you add a value?

What it is supposed to do:
Every time you change the document,
If you are changing one cell at a time (no copy/paste),
If the change occurs in Sheet 1, column A,
Copy the newly added formula from the target cell to the next open position in sheet 4, column A.