putting 2 lines VBA together

[g48dd]

Excel 2003: I have this:

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

its from a paste special macro, I need to put it with the following macro. This macro will copy and paste acording to the date. It works great but I need it to paste special just the values. Here is the code:

Sub MoveDayOfWeek()
    Dim DayOfWeek As String
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    DayOfWeek = Format(CDate(Date), "dddd")
    Select Case DayOfWeek
    Case "Monday"
        Range("A1:A" & LR).Cut Destination:=Range("B1")
    Case "Tuesday"
        Range("A1:A" & LR).Cut Destination:=Range("C1")
    Case "Wednesday"
        Range("A1:A" & LR).Cut Destination:=Range("D1")
    Case "Thursday"
        Range("A1:A" & LR).Cut Destination:=Range("E1")
    Case "Friday"
        Range("A1:A" & LR).Cut Destination:=Range("F1")
    Case "Saturday"
        Range("A1:A" & LR).Cut Destination:=Range("G1")
    Case "Sunday"
        Range("A1:A" & LR).Cut Destination:=Range("H1")
    End Select
    Application.CutCopyMode = False
End Sub

Thank you ken

 

[VoG]

Try like this

Case "Monday"
    With Range("A1:A" & LR)
        .Copy
        Range("B1").PasteSpecial Paste:=xlPasteValues
        .ClearContents
    End With

 

[g48dd]

Thanks I am back in my hooch right now I will try that as soon as I get to work tomorrow.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[JackDanIce]

Alternative suggestion to VoG's code is:

Case "Monday"
    With Range("A1:A" & LR)
        Range("B1:B" & LR) = .Value
        .ClearContents
    End With

 

[g48dd]

Sub MoveDayOfWeek()
    Dim DayOfWeek As String
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    DayOfWeek = Format(CDate(Date), "dddd")
    Select Case DayOfWeek
Case "Monday"
    With Range("A10:A13" & LR)
        .Copy
        Range("B10").PasteSpecial Paste:=xlPasteValues
        End With
Case "Tuesday"
    With Range("A10:A13" & LR)
        .Copy
        Range("C10").PasteSpecial Paste:=xlPasteValues
        End With
Case "Wednesday"
    With Range("A10:A13" & LR)
        .Copy
        Range("D10").PasteSpecial Paste:=xlPasteValues
        End With
Case "Thursday"
    With Range("A10:A13" & LR)
        .Copy
        Range("E10").PasteSpecial Paste:=xlPasteValues
        End With
Case "Friday"
    With Range("A10:A13" & LR)
        .Copy
        Range("F10").PasteSpecial Paste:=xlPasteValues
        End With
Case "Saturday"
    With Range("A10:A13" & LR)
        .Copy
        Range("G10").PasteSpecial Paste:=xlPasteValues
        End With
Case "Sunday"
    With Range("A10:A13" & LR)
        .Copy
        Range("H10").PasteSpecial Paste:=xlPasteValues
        End With
    End Select
    Application.CutCopyMode = False
End Sub

This is the first VBA Code, I made some adjustments, first I had to set it up to get the data from where the data is located A10:A13. It worked fine but it erased my Friday Heading, so I adjusted it. Headings were actaully in B1:H1 but I moved them to B9:H9, which meant I needed to make sure the data was moved to B10:H10 depending on the day. When I ran it like this everything worked but it kept removing the sorce data. So I just read through your code and tried to figure out what was what, I don't write VBA but I can sometimes look at it and figure it out, the .ClearContents I figured that is what was removing the sorce data, I didn't tell you i needed it left in place, so once I removed that, it works. Depeninding on the Day of the week this macro will go to the sorce data (A10:A13) copy and paste special just the values according to the day of the week and leave the sorce data intact.

The second code also works but has a few snags that I can't figure out yet. It will place the data according to the day of the week and paste special so all I am getting is the value from the sorce data, but when it does it, it erases my heading Friday and it only copies 3 of the values I need. This second one is less code and I will work with it and see if I can adjust it to make it work.

Thank you everyone for all the help
Ken

I use this stuff at work, it makes my day easier, but I do not claim work that is not mine. People ask how i know all this stuff and I explain I don't I just know who to go ask and they can do the same thing and I suggest reading, reading, reading, thanks again

 

[VoG]

Glad it works (as I understand it).

By way of explanation, we both used ClearContents because your original code used Cut. PasteSpecial requires Copy (not Cut) which is why I used Copy and added the ClearContents to mimic the effect of Cut.

 

[g48dd]

I understand, there is one small problem. When the macro performs the PASTE, the whole Column is high lighted. This means anything under where I wanted it pasted is eliminated or pasted over... is there a way to limit the range of the paste. I am now using this on an actual SS at work, that range would be 7 rows counting down from where the PASTE starts. Of course the starting cell is different every day... so right now Friday the starting cell is L10 and the bottom of the range would be L16. I have a work around for this I just moved everything that was under this paste it is no big deal, but I was wondering if you can name / define the actual range it will paste too.

Thanks
Ken

 

[JackDanIce]

Not sure I understand what you mean, however, try:

Sub MoveDayOfWeek()
    Dim DayOfWeek As String
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    DayOfWeek = Format(CDate(Date), "dddd")
    Select Case DayOfWeek
Case "Monday"
    With Range("A10:A13" & LR)
        .Copy
        Range("B10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
Case "Tuesday"
    With Range("A10:A13" & LR)
        .Copy
        Range("C10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
Case "Wednesday"
    With Range("A10:A13" & LR)
        .Copy
        Range("D10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
Case "Thursday"
    With Range("A10:A13" & LR)
        .Copy
        Range("E10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
Case "Friday"
    With Range("A10:A13" & LR)
        .Copy
        Range("F10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
Case "Saturday"
    With Range("A10:A13" & LR)
        .Copy
        Range("G10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
Case "Sunday"
    With Range("A10:A13" & LR)
        .Copy
        Range("H10").End(xlDown).Offset(1,0).PasteSpecial Paste:=xlPasteValues
        End With
    End Select
    Application.CutCopyMode = False
End Sub