asked on

COPY SOME VALUES FROM A DIFFERENT SHEET AND PASTE WITH SPECIAL FORMAT

Hi,

Attached is a sample file where you could see the raw data as I have it in Sheet "DATA" and then in Sheet "STICKER" you can see the results that I'm looking for.

Basically I need for each row to copy all the values (from Sheet DATA) located in the following columns: B,C,D,I,J,M and paste it with the format you will see in Sheet STICKERS.

The last "Sticker" in the sample (Higlighted in light blue) contains the Column NAME from sheet DATA that is supposed to be placed there and the format need it.

I don't think that this is important, but please note that the Paper size of Sheet "STICKERS" is only 5,5 cms x 2,2 cms which is the stickers size We use here.

I really appreciatte your help and time,

Regards,
Roberto.
EE-STICKERS.xls

ragnarok89

Use this:

Sub y()
Sheets("data").Select
temparray = Range("A2:M6")

Sheets("Hoja3").Select
Range("A1").Select
r = 0
For i = 1 To UBound(temparray)
    Range("A" & r + 1).Value = temparray(i, 4)
    Range("A" & r + 1 & ":B" & r + 1).Select
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .ShrinkToFit = True
    End With
    With Selection.Font
        .FontStyle = "Bold"
    End With
    
    Range("A" & r + 2).Value = temparray(i, 3)
    Range("A" & r + 2 & ":B" & r + 2).Select
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .ShrinkToFit = True
    End With
    
    Range("A" & r + 3).Value = temparray(i, 9)
    Range("A" & r + 3).HorizontalAlignment = xlLeft
    Range("B" & r + 3).Value = temparray(i, 10)
    
    Range("A" & r + 4).Value = temparray(i, 2)
    Range("A" & r + 4).HorizontalAlignment = xlLeft
    Range("B" & r + 4).Value = temparray(i, 13)
    Range("B" & r + 4).Font.Bold = True
    Range("B" & r + 4).HorizontalAlignment = xlRight
    
    r = r + 4
Next i

r = r + 1
Range("A" & r & ":B" & r).Select
Range("A" & r & ":B" & r).HorizontalAlignment = xlCenter
Selection.Merge
Selection.Font.Bold = True
ActiveCell.FormulaR1C1 = "D"

Range("A" & r + 1 & ":B" & r + 1).Select
Range("A" & r + 1 & ":B" & r + 1).HorizontalAlignment = xlCenter
Selection.Merge
ActiveCell.FormulaR1C1 = "C"

Range("A" & r + 2).Value = "I"
    Range("A" & r + 2).HorizontalAlignment = xlLeft
    Range("B" & r + 2).Value = "J"
    Range("B" & r + 2).HorizontalAlignment = xlRight

Range("A" & r + 3).Value = "B"
    Range("A" & r + 3).HorizontalAlignment = xlLeft
    Range("B" & r + 3).Value = "M"
    Range("B" & r + 3).Font.Bold = True
    Range("B" & r + 3).HorizontalAlignment = xlRight
    
    Range("A" & r & ":B" & r + 3).Select
    With Selection.Interior
        .ColorIndex = 34
    End With
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

StephenJR

Sorry, didn't refresh.

Pabilio

ASKER

Hi Ragnaro,

Your code is giving me some problems with the select range
Line 3 and others...
I changed but still could not work with it.

Stephen your code works perfectly...!!

I'll wait for Ragnaro's fix (If he wants to) in order to close the question

Thank you both for your help.

Cheers,
Roberto.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Pabilio

ASKER

Stephen,
I'm really sorry that I didnt close this question.
I tried now but It does not allow me to close it.
Please teylyn proceed as you said in your comment.
And have a happy new year both of you.
Best regards,
Roberto.