PDA

View Full Version : transfer data



ahmed haleem
09-21-2016, 01:45 PM
Details inside the attached file

mana
09-21-2016, 06:13 PM
Option Explicit


Sub test()
Dim ws As Worksheet
Dim r As Range

With Worksheets("sales").Range("a1").CurrentRegion
For Each ws In Worksheets
If ws.Name <> .Parent.Name Then
ws.UsedRange.Columns("a:g").Offset(1).ClearContents
.AutoFilter 6, ws.Name
If .Columns("a").SpecialCells(xlCellTypeVisible).Count > 1 Then
Set r = Intersect(.Cells, .Offset(1))
r.Columns("a").Copy ws.Cells(2, "a")
r.Columns("b").Copy ws.Cells(2, "c")
r.Columns("c").Copy ws.Cells(2, "e")
r.Columns("d").Copy ws.Cells(2, "f")
End If
.AutoFilter
End If
Next
End With


End Sub

ahmed haleem
09-22-2016, 12:14 AM
Hi mana
I want use events worksheet change (sheet1)
Can you help me?

mana
09-22-2016, 12:35 AM
Did you try my code?

ahmed haleem
09-22-2016, 01:08 AM
I do not want clearcontents of sheets"sales" and The data will be repeated
This is a wonderful code already

mana
09-22-2016, 01:26 AM
I do not want clearcontents of sheets"sales" and

Sorry, I can't understand your comments.


Option Explicit

Private Sub Worksheet_Deactivate()
Dim ws As Worksheet
Dim r As Range

With Range("a1").CurrentRegion
For Each ws In Worksheets
If ws.Name <> Me.Name Then
ws.UsedRange.Columns("a:g").Offset(1).ClearContents
.AutoFilter 6, ws.Name
If .Columns("a").SpecialCells(xlCellTypeVisible).Count > 1 Then
Set r = Intersect(.Cells, .Offset(1))
r.Columns("a").Copy ws.Cells(2, "a")
r.Columns("b").Copy ws.Cells(2, "c")
r.Columns("c").Copy ws.Cells(2, "e")
r.Columns("d").Copy ws.Cells(2, "f")
End If
.AutoFilter
End If
Next
End With

End Sub

ahmed haleem
09-22-2016, 01:49 AM
Sorry
I want to transfer data from sheet sales to other sheets by customer and sheets names

mana
09-22-2016, 02:15 AM
Sorry
I want to transfer data from sheet sales to other sheets by customer and sheets names

Sorry, , I'm not so good at English. I can't understand your comments.
Did you try my code?

Tom Jones
09-22-2016, 02:26 AM
@hand,
each sheet headers are different in sheets "customer1 to customer4" So copying should be done according to the header, I think.

ahmed haleem
09-22-2016, 02:39 AM
my dear tom jones
Yes, this is who I want, already