macro to copy data to other sheet

[TooN]

Hi..

Is there a macro for copying filtered data from one sheet to another sheet.
Ive been surfing the internet for a few days now and i saw some macro's that
almost did the job. Ive got a lot of lines and colums so im using the filter
option a lot. The problem is that it takes to much time to copy it everytime.

What i would like is a macro that copies the filtered data to a new sheet in
the same workbook with formulas and the header. It would be good if it
selects automaticly all the filtered data when using the macro. An option is
if it asks in a popup box the name of the new sheet (that would be
perfect).... please help me with this....

Thanks

 

[Graham Whitehead]

Hi,

It would help if you could give a small example of you arre trying to do.
Post a workbook and I'll take a look.

 

[TooN]

Hi Graham,

I will try to explain. Im working with SAP and from there i make huge lists
with things that have to be done (to do list). The list got about 25 columns
and about 1000 lines. The list contains data that says something about the
department, the material, the duration and the activity. Further more it got
a few dates (nothing special). After filtering the main list (for example im
filtering it on a specific department so i can show that department what has
to be done), i want to copy that data to another sheet. I was thinking of a
macro that copies the filtered data to a new sheet (in the same workbook). It
would be great if i will be asked what the name of the new sheet will be! I
will explain it step by step:

1. Filter the data you want from a list (could be any list ofcourse)
2. Copy the filtered data to a new sheet. (thats what causes me the problem)

The problem is that the data that is in the main sheet is full with
formulas. Further more i would like to see that if i change (for example a
date) on the main sheet it changes automaticly on the copied sheet. I hope
you understand the situation, if not let me know and i try to explain it
again....

Thanks

 

[Graham Whitehead]

Hi,

Just threw this together by getting a random list from the web. The file is
maybe too big to attach but this code works:

Sub CONTROL()

Dim MultiArr()
Dim x As Integer
Dim y As Integer
Dim intColumns As Long
Dim lngLastRow As Long
Dim strWorksheetName As String

'determine the number of rows in the data
'note that the column reference is C - can be changed
With ActiveSheet
lngLastRow = .Range("C65536").End(xlUp).Row
End With

'determine the number of columns and set exit condition
'i.e. when three rows in one column contain nothing
For y = 0 To 1000
If Range("C2").Offset(0, y).Value = 0 Then
If Range("C2").Offset(1, y).Value = 0 Then
If Range("C2").Offset(3, y).Value = 0 Then
Exit For
End If
End If
End If
Next y

'set variable to record the number of columns

intColumns = y

'set loop and record these values in an array
ReDim MultiArr(lngLastRow, intColumns)

For x = 0 To lngLastRow - 1
For y = 0 To intColumns - 1
MultiArr(x, y) = Range("C2").Offset(x, y).Value
Next y
Next x

'inset new worksheet and put an input box to rename it
Sheets("Sheet1").Select
Sheets.Add
strWorksheetName = InputBox("Enter name for new worksheet")
ActiveSheet.Name = strWorksheetName

'select this sheet and put the valuus from the array into it
Sheets(strWorksheetName).Select

For x = 0 To lngLastRow - 1
For y = 0 To intColumns - 1
Range("C2").Offset(x, y).Value = MultiArr(x, y)
Next y
Next x

End Sub

 

[TooN]

hi...

Thanks a lot, the macro is almost running perfect. There is only one thing i
have to change but i dont know how! I will try to explain:
Let's say the master sheet (the sheet with all the data im filtering) has 50
rows. After filtering it shows row 10 to 15 and ofcourse the header (I use
freeze panes). So far its going good. Than i run the macro. It starts with
asking me the new sheet name. I fill in the sheet name and than it copies the
data to the new sheet. This is all doing perfect BUT, it copies the lines
till 15 instead of 10 to 15. Lets say the next filter i do from the
mastersheet it shows lines 23 till 29. When im running the macro it copies
the data perfect but it copies till line 29 than and not 23 till 29. I tried
to adjust that in the macro but it didnt work out good!

Could you please help me out with that? Thanks

 

[TooN]

I forgot one little detail!! Is it possible to copy the same layout as the
mastersheet to the new sheet. Ive got some columns and lines cloured and made
a few adjustments to the columnwide??

thanks