Posts by frankximus

Re: Colour code the data range after data is filtered and pasted from another sheet

Hey cytop,

Thanks for your prompt reply!

I think I might need to expand on that a bit since this won't be direct define of colour.

Here are the codes I've used in the sheet.

Sub Test()'
' Test
    
    Dim lr As Long
    Dim ws As Worksheet




    Set ws = Sheets("display")
    lr = ws.Cells(Rows.Count, "c").End(xlUp).Row
    
    Application.ScreenUpdating = False
    Sheets("Data source").Select
    If ActiveSheet.AutoFilterMode Then Cells.AutoFilter
    ActiveSheet.Range("$A$1:$S$102").AutoFilter Field:=5, Criteria1:="<>"
    Range("A2:d102,e2:e102").Select
    Selection.Copy
    Sheets("Display").Select
    
    Range("c" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'copy and paste the title of the course
    
    Sheets("Data source").Select
    Range("e1").Select
    Selection.Copy
    Sheets("Display").Select
    Range("c" & Rows.Count).End(xlUp).Offset(-1, -2).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False




Application.ScreenUpdating = True
End Sub

I'm trying to come up with a way to auto color the cells whenever a new set of data is pulled out of the data sheet. This is for purely visual differentiation. Since the position of the pulled data varies, I'm wondering if there is a way/code to auto color the range of cells that were active? (this range varies automatically)

Hey guys,

I've got a workbook with coding that pulls data from another sheet by criteria, however I want to be able to color code the whole data pasted so that they are differentiated visually from each other (Math has red, Geography has yellow etc...) in the display sheet.

So far I've found a code that color codes the cell's background which is limited to the active cell, does anyone know if I can apply to the whole range? Say when data is pasted into A3:B4, then the whole range is color coded as red?

Thanks!

Re: VBA filter & pull data from another sheet, but currently data overlaps after pull

Hey guys, I've solved the problem using the

Range("c" & Rows.Count).End(xlUp).Offset(-1, -2).Select

coding

Hey guys,

I'm trying to build something that pulls data directly from another sheet by filtered criteria. If the criteria matches, then data is pulled into another display sheet. However, my challenge is that whenever the data is pulled, it overlaps with each other.

Is there a way or code that can make it smarter? Say if under the 'Math' there are lots of data, then the row starting 'geography' automatically goes down to avoid overlapping. Can anyone give me some directions as to what function to search for things like this?

I've also posted the same question on another forum seeking a smarter way of coding, link is attached below.

http://www.mrexcel.com/forum/e…sheet-based-criteria.html

Thanks heaps guys!