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]

I think that for it to work I have to put it under certain worksheet, not under the workbook itself, can you tell me to put on which workbook, I put under "sheet4" and error happens: Subscript out of Range

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
   [B] If Not Intersect(Target, Sheets("Sheet1").Range("A:A")) Is Nothing Then[/B]
        Range("=Sheet4!A" & Target.Row * 7).Formula = Target.Formula
        Range("='Sheet4!L" & Target.Row * 7).Formula = Target.Formula
        Range("=Sheet4!M" & Target.Row * 7).Formula = Target.Formula
        Range("=Sheet4!N" & Target.Row * 7).Formula = Target.Formula
        Range("=Sheet4!O" & Target.Row * 7).Formula = Target.Formula
        Range("=Sheet4!Q" & Target.Row * 7).Formula = Target.Formula
    End If
End Sub

I refer different columns in Sheet 4 as the same method of Sheet4!A, for example Sheet1!X to Sheet4!O etc...
Sorry for making a hassle for you moonfish, but I really need to get this done by 4 hours time. Anyone can help me?

P.S. The error is in bold

 

[smilelover]

Maybe I am asking too much, since I will copy paste as they are lots of data to be input and deleted in a short time,
Is there a way to set up a command button on Sheet 4, so it can somehow "refresh" the sheet to reflect the changes in Sheet 1?

 

[moonfish]

I guess it should go into sheet1. If you want to copy more than just values in column A, you will have to alter the second IF. Right now it will only copy values in Sheet1!A. How about this:

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

There's a shorter and more efficient way to write this down, I'm sure. I'm just not very good with VBA.

If you want to copy everything in every column over to the other file with 7 blank rows in between the data, that'll take up a lot less space:

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

Still not testing this, no Excel available today.

As for the button: You can always copy it to a module and rename it. If you create a button Excel will ask you to select the macro to use for this, choose the newly made macro.

 

[smilelover]

Error:
Subscript out of range

If Not Intersect(Target, Sheets("Sheet1").Range("A:A")) Is Nothing Then

 

[moonfish]

Try it on the range A1:A1000 instead of A:A to see if there's still a problem and let me know.

 

[ChrisM]

Having a hard time following all your posts, but if I'm reading this right, you put the worksheet_change event in the sheet4 module, yet your code is trying to reference Sheet1.

So you are asking Excel to take the target cell from sheet 4 and somehow intersect it with a value from sheet 1, which is why you are getting the error.

If I'm following your code right.

 

[smilelover]

Moonfish: It is not working I am sorry
ChrisM: In short, I want to import data from some columns in Sheet 1 to Sheet 4 with 7 blank gaps between them. I hope that they can sync automatically so if I made any changes on Sheet 1, either modifying or adding new values under the columns in Sheet 1, they will make the same changes in Sheet 4 as well, with 7 gaps between them.

 

[moonfish]

Smilelover, could you post your current macro's in full detail, complete with the location you've inserted them?

 

[smilelover]

Well actually I am fine using another "old method", so yeah it is done Thanks

Can you help me to look on another code?
A short code with an error as well
http://www.mrexcel.com/forum/showthread.php?p=2804458#post2804458
Thanks moonfish