VBA error, stuck and lost

[Peltz]

Hello. I am completly new to programming and VBA. I tried writing my first primitive macros from scratch and soon got into trouble, first with errors, then it didn't seem to do anything. I would greatly appreciate some advise or solutions from more experienced VBA users.

Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("HK-oppfolging").Activate
For Each Cell In Range("D4,D29,D54,D79,D104,D129,D154,D179,D205").Cells
If Cell.Text = Sheets("Status").Range("D7").Value Then
ActiveSheet.Range(Cells(-3, -2), Cells(3, 22)).Select
End If
Selection.Copy
Sheets("Status").Select
ActiveSheet.Range(Cells(2, 11), Cells(8, 35)).Select


Next Cell
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub



What I have is two worksheets: "Status" and "HK-Oppfolging". "HK-oppfolging" constitutes of 9 tables stacked in an uniform pattern. In each of the 9 tables there is a name in one cell (D4,D29,D54,D79,D104,D129,D154,D179,D205)
The tables are in the range (-3collumns, -2rows): (3collums, 22rows) compared to the cell with the name: E.g. for D4, the range is A2:G26.

The worksheet "Status" cell D7 has a name.

Now, what I want the macro to do is to find the name in D7 wich will be in one of the "HK-oppfolging" sheet cells (D4,D29,D54,D79,D104,D129,D154,D179,D205) and when it finds it, then copy the corresponding table to the range B11:H35 in the worksheet "Status".

Anyone?

 

[cstimart]

For starters, change from "Private Sub" to "Sub"...and use a generic name for the Sub in a Module.

"Private Sub Worksheet_Change(ByVal Target As Range)" looks like something from the "This Workbook" object.

Also, using "Cell" as a variable will get confusing...try "rng" as the vairable name for your Range.

 

[cstimart]

How's this? I revised in a rush...

Sub test()
Dim i As Long
Dim Status As String
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With
Status = Sheets("Status").Range("D7").Value
Sheets("HK-oppfolging").Select
For i = 4 To 204 Step 25
    If Range("D" & i) = Status Then
        Sheets("HK-oppfolging").Range("A2:G26").Copy Destination:=Sheets("Status").Range("B11:H35")
    End If
Next i
End Sub

I'm sure I've missed some details on the ranges, but this should give you a start.

 

[Peltz]

Pending...

 

[cstimart]

Based on your comment about triggering the Macro to run anytime D7 is changed, I would still leave the Sub in the Module and have the Worksheet_Change call the macro.

 

[Peltz]

First: Thank you for your time.

Im sorry but the code didnt work. I ended up at HK-oppfølging, and nothing happend.

Sheets("HK-oppfolging").Range("A2:G26").Copy Destination:=Sheets("Status").Range("B11:H35")


As I understand this statement the range A2:G26 is static?


What I need is the range to change depending on what cell that match "Status" D7 value. The "range rule" is (-3collumns, -2rows): (3collums, 22rows) in relation to the cell (For i = 4 To 204 Step 25
If Range("D" & i) = Status)


BTW I use Excel 2003.

Regards

 

[cstimart]

First: Thank you for your time.


Sheets("HK-oppfolging").Range("A2:G26").Copy Destination:=Sheets("Status").Range("B11:H35")


As I understand this statement the range A2:G26 is static?


What I need is the range to change depending on what cell that match "Status" D7 value. The "range rule" is (-3collumns, -2rows): (3collums, 22rows) in relation to the cell (For i = 4 To 204 Step 25
If Range("D" & i) = Status)


BTW I use Excel 2003.

Regards

Yes, that is static, however, you can change the # to be calculated as needed. [ i.e. Range("A" & i + 2 &":G" & i + 24) ]

 

[Peltz]

Neat!

IT WORKS!!!


Now ill just figure out how I'l lmake the Worksheet_Change call the macro. This beeing my first project using macros, and using more than one as I discover new solutions, I havent given much time organizing them, focusing more on how to write them. I have alot to learn...

Thank you so much, you have been of great help

 

[cstimart]

This might help you with the trigger.

http://www.mrexcel.com/forum/showthread.php?t=572933&highlight=macro+cell+trigger