running some macros automatically

[justmaryaanna]

I have attached extra rudimentary of my attempt to solve what I need.
I want to do the following things with this excel:

1.macroul copydateclient, to copy data (F2: l2) from sheet master if e2 meets one of three conditions: E2 = aa or bb or cc, in Sheet kk at b2:h2, and after that the data will not be possible to change , otherwise the cells may be changed(b2;h2)

2.macroul copynrinreg, will send me to another sheet and copy nr.inregistrare, I have 8 sheet and the macro will take me to one of this sheets if o2 in the sheet master is O2 = DD or EE or FF or GG or HH or ii or jj or kk


3.and this 2 macros to run one after anothers automatically


I know it's a little abstract but I can’t give actual data ... but I can assure you that this is what I want to realize with this excel.

I really need your help! Tanks very much! Mary

 

[SkipVought]

hi,

Welcome to Tek-Tips.

Difficult to understand what you need. Many of us are unable to download due to company restrictions.

Many times when people talk about COPY from one sheet to another, other techniques can accomplish the task, like MS Query or a PivotTable.

Need more specific clear, concise & complete statement of your requirements, includeing some data examples, even if it is dummy data, all presented HERE, in this thread, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

Sorry but I don't speak English very well.
I have 2 macros. Macro 1 named CopyNrInreg and Macro 2 named CopyDateClient.
The first macro"CopyNrInreg", copys the value of column C from the sheet named Master into another sheet; the sheet that will be active depends on the value of column O, you will see what I mean in the example,
The second macro"CopyDateClient" copys the value of cell from f2:l2 from sheet named Master in the sheet named kk in b2:h2, but the cell will be copied only is the cell e2 in sheet Master has one of this values: aa, bb, cc. I want to lock the cell that b2:h2 from sheet kk after they will be copied. But if cell e2 in sheet Master equals to dd, ee, etc. I whant to be able to write in cell b2:h2 from sheet kk.
And third off all I whant to run this 2 macros one after the other, first CopyNrInreg, and second CopyDateClient, automatically. As a vba code.
Thanks i hope you will understand now!

 

[SkipVought]

you will see what I mean in the example

NO! I cannot, and neither can many other people, who, like me, are prohibited from downloading data, by company restictions.

If you have 2 macros, CopyNrInreg, and CopyDateClient, that you want to run one after the other...

sub Run2Macros()
  CopyNrInreg
  CopyDateClient
end sub

If you need something other than this, then you must post HERE, not in a link, the VBA in your 2 macros and sample data relevant to your issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

This is the macro 2:

Sub CopyDateClient()

Application.ScreenUpdating = False

Sheets("Master").Select

Select Case ActiveCell.Value

Case "aa"

Sheets("kk").Range("b65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 1).Value
Sheets("kk").Range("c65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 2).Value
Sheets("kk").Range("d65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 3).Value
Sheets("kk").Range("e65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 4).Value
Sheets("kk").Range("f65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 5).Value
Sheets("kk").Range("g65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 6).Value
Sheets("kk").Range("h65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 7).Value
Application.Goto Sheets("kk").Range("i65536").End(xlUp).Offset(0, 1)

Case "bb"

Sheets("kk").Range("b65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 1).Value
Sheets("kk").Range("c65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 2).Value
Sheets("kk").Range("d65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 3).Value
Sheets("kk").Range("e65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 4).Value
Sheets("kk").Range("f65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 5).Value
Sheets("kk").Range("g65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 6).Value
Sheets("kk").Range("h65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 7).Value
Application.Goto Sheets("kk").Range("i65536").End(xlUp).Offset(0, 1)

Case "fost_client"

Sheets("kk").Range("b65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 1).Value
Sheets("kk").Range("c65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 2).Value
Sheets("kk").Range("d65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 3).Value
Sheets("kk").Range("e65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 4).Value
Sheets("kk").Range("f65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 5).Value
Sheets("kk").Range("g65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 6).Value
Sheets("kk").Range("h65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, 7).Value
Application.Goto Sheets("kk").Range("i65536").End(xlUp).Offset(0, 1)

End Select
Application.ScreenUpdating = True
End Sub

How can i make the activecell to be E2 from sheet "master" and move one cell down every time I whant to run the macro. I've tried this code :

activecell.Offset(0, 1).Range("e2").Select

but it does't work.

How to lock the cells after the macro has executed. Like macro run's sheet kk are populated with date, after that the cells can't be modifi,to insert other values. But if the macro does't run i will be able to modifi the cell. I've tried this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim b As Boolean, cols As Long, cell As Range
On Error GoTo 1
Sheets("Master").Select
Application.EnableEvents = False
For Each cell In Target
If UCase(Range("e" & cell.Row).Value) = "aaa" And Sheets("KK").cell.Column = 2 Then
b = True
Exit For
End If
Next cell
If b Then
selrg.Select
Else
Set selrg = Target
End If
1 Application.EnableEvents = True
End Sub

but it doesn't work.

 

[SkipVought]

Sub CopyDateClient()
    Dim r As Range, lRow As Long, iCol As Integer

    Application.ScreenUpdating = False
    
    With Sheets("Master")
        For Each r In .Range(.Cells(2, "E"), .Cells(2, "E").End(xlDown))
            With Sheets("kk")
            
                With .UsedRange.CurrentRegion
                    lRow = .Row + .Rows.Count
                End With
                
                Select Case r.Value
                    Case "aa", "bb", "fost_client"
                        For iCol = 2 To 8
                            .Cells(lRow, iCol).Value = r.Offset(0, iCol - 1).Value
                        Next
                End Select
            End With
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

thanks very much! goes well
What about this code:

Sub CopyNrInreg()

Application.ScreenUpdating = False


Sheets("Master").Select

Select Case ActiveCell.Value
Case "dd"
Sheets("dd").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("dd").Range("A65536").End(xlUp).Offset(0, 1)

Case "ee"
Sheets("ee").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("ee").Range("A65536").End(xlUp).Offset(0, 1)

Case "ff"
Sheets("ff").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("ff").Range("A65536").End(xlUp).Offset(0, 1)

Case "gg"
Sheets("gg").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("gg").Range("A65536").End(xlUp).Offset(0, 1)

Case "hh"
Sheets("hh").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("hh").Range("A65536").End(xlUp).Offset(0, 1)

Case "hh-1"
Sheets("hh").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("hh").Range("A65536").End(xlUp).Offset(0, 1)

Case "ii"
Sheets("ii").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("ii").Range("A65536").End(xlUp).Offset(0, 1)

Case "jj"
Sheets("jj").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("jj").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-1"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-2"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-3"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-4"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-5"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-6"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

Case "kk-7"
Sheets("kk").Range("A65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Offset(0, -12).Value
Application.Goto Sheets("kk").Range("A65536").End(xlUp).Offset(0, 1)

End Select

Application.ScreenUpdating = True

End Sub

Is it written right?

And in the end to make them run one after another is this written right?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("o265536")) Is Nothing Then
Exit Sub
End If
Call CopyNrInreg
Call CopyDateClient
End Sub

 

[SkipVought]

I seldom use Active anything, as no one has any idea WHAT the Active reference is?????

So WHAT does the ActiveCell refer to in the sheet named Master?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

the activecell is o2 in the sheet master.

 

[SkipVought]

Sub CopyNrInreg()
    Dim r As Range, lRow As Long

    Application.ScreenUpdating = False
    
    With Sheets("Master")
        For Each r In .Range(.Cells(2, "O"), .Cells(2, "O").End(xlDown))
            With Sheets(Left(r.Value, 2))
            
                With .UsedRange.CurrentRegion
                    lRow = .Row + .Rows.Count
                End With
                
                .Cells(lRow, "A").Value = r.Offset(0, iCol - 12).Value
            End With
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'this says that WHEN the CHANGED value is in column O 
' and the CHANGED value is in the Used Range  
' THEN run the macros.
    If Not Intersect(Target, Range("o2").EntireColumn, Sheets("Master").UsedRange) Is Nothing Then
        CopyNrInreg
        CopyDateClient
    End If
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

Sub CopyNrInreg()
Dim r As Range, lRow As Long

Application.ScreenUpdating = False

With Sheets("Master")
For Each r In .Range(.Cells(2, "O"), .Cells(2, "O").End(xlDown))
With Sheets(Left(r.Value, 2))

With .UsedRange.CurrentRegion
lRow = .Row + .Rows.Count
End With

.Cells(lRow, "A").Value = r.Offset(0, iCol - 12).Value
End With
Next
End With
Application.ScreenUpdating = True
End Sub

it does'n work. it apears a error 9. does't reconize With Sheets(Left(r.Value, 2))

 

[SkipVought]

Sub CopyNrInreg()
    Dim r As Range, lRow As Long    '[b]
    Dim sSheet As String            '[/b]

    Application.ScreenUpdating = False
    
    With Sheets("Master")
        For Each r In .Range(.Cells(2, "O"), .Cells(2, "O").End(xlDown))    '[b]
            sSheet = Left(r.Value, 2)
            Select Case sSheet
                Case "dd", "ee", "ff", "gg", "hh", "ii", "jj", "kk"         '[/b]
                    With Sheets(sSheet)
                    
                        With .UsedRange.CurrentRegion
                            lRow = .Row + .Rows.Count
                        End With
                        
                        .Cells(lRow, "A").Value = r.Offset(0, iCol - 12).Value
                    End With                                                '[b]
            End Select                                                      '[/b]
        Next
    End With
    Application.ScreenUpdating = True
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

http://dl.transfer.ro/transfer_ro-24jan-c90287e19391b.zip

here is my file it does't work realy...(

 

[SkipVought]

As I have repeatedly stated I CANNOT DOWNLOAD at work!

Your macro says that each value in COLUMN O, first 2 characters, is a SHEET NAME.

Is this true?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[justmaryaanna]

Not really. sorry i said my English is bad.

 

[justmaryaanna]

Sorry but i have another ideea. Can you make this code :

Sub CopyDateClient()
Dim r As Range, lRow As Long, iCol As Integer

Application.ScreenUpdating = False

With Sheets("Master")
For Each r In .Range(.Cells(2, "E"), .Cells(2, "E").End(xlDown))
With Sheets("kk")

With .UsedRange.CurrentRegion
lRow = .Row + .Rows.Count
End With

Select Case r.Value
Case "aa", "bb", "cc"
For iCol = 2 To 8
.Cells(lRow, iCol).Value = r.Offset(0, iCol - 1).Value
Next
End Select
End With
Next
End With
Application.ScreenUpdating = True
End Sub

to copy cell c, f, g, h, i, j, k, l from sheet master.
it will work better for me.

 

[justmaryaanna]

and then to change this code:
Sub CopyNrInreg()
Dim r As Range, lRow As Long '
Dim sSheet As String '

Application.ScreenUpdating = False

With Sheets("Master")
For Each r In .Range(.Cells(2, "O"), .Cells(2, "O").End(xlDown)) '
sSheet = Left(r.Value, 2)
Select Case sSheet
Case "dd", "ee", "ff", "gg", "hh", "ii", "jj", "kk" '
With Sheets(sSheet)

With .UsedRange.CurrentRegion
lRow = .Row + .Rows.Count
End With

.Cells(lRow, "A").Value = r.Offset(0, iCol - 12).Value
End With '
End Select '
Next
End With
Application.ScreenUpdating = True
End Sub

By if i select kk in cell o of sheet master go to sheet kk and run macro copydateclient, if i select jj in cell o of sheet master just go to sheet jj.
Location:

Edit/Delete Message Reply With Quote

 

[justmaryaanna]

p.s and copy cell c from sheet master in sheet jj at cell a
in sheet kk c will be copy in cell a, f in b etc