MrExcel.com - Your One Stop for Excel Tips & Solutions

Copying data from one sheet to another and "IF" ....

Posted by Karv on October 31, 2001 2:46 AM

Hi all.
I've looked right down the page and couldn't find quite what I'm after so I'd really appreciate a little help :)

I have a workbook with 11 sheets.
The first sheet (TMPsheet) contains my daily raw data, column A of TMPsheet contains the codes (GO SC PU WA WO etc) which I need to sort by to send the data to its proper sheet.

How can I make the individual sheet (eg WO) automatically read all columns and rows from TMPsheet if column A contains WO without having about 500 blank lines where column A of TMPsheet contains something other than WO.

Thanks in advance,
Feel good, train a n00bie :)

karv

Posted by Henry Root on October 31, 2001 3:40 AM


Assuming that between the first and last data columns on the TMP sheet there are no completely blank columns, check whether this does what you need :-

Sub UpDate_Sheets()
Dim ws As Worksheet, source As Range, dest As Range
Dim daily As Worksheet
Set daily = Worksheets("TMP")
Application.ScreenUpdating = False
On Error Resume Next
For Each ws In Worksheets
If ws.Name <> daily.Name Then
daily.Range("A1").AutoFilter Field:=1, Criteria1:=ws.Name
Set source = daily.Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlVisible)
Set dest = Worksheets(ws.Name).Range("A65536").End(xlUp).Offset(1, 0)
source.Copy dest
End If
Next
daily.Range("A1").AutoFilter
On Error GoTo 0
End Sub


Posted by Karv on October 31, 2001 5:04 AM


Thanks for the quick response, however I THINK I'm too much of a n00b to get this to work.
I've been bashing Excel for approx 9 hours of my entire life :)

Any more hints would be gratefully received :)
K

Posted by Zoltan Czibor on October 31, 2001 10:25 AM

http://www.mrexcel.com/tip038.shtml