• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to paste only certain values of cell from one sheet to another

Sanoj

Sanoj

Member
Hello,

Can some one help me with the below code, what I am looking for is, from sheet "Form" certain values of cells mentioned in 2 sets of Array.
1st set of Array should get copied to sheet "Tracker" C3 onward and second set of array from next cell after the 1set of array ends say EF3 onwards.

whereas now first sett is its pasting from A3 and second from A4. Please let me know in case of any question.

Following is the code which I am using now:

Code: 
Sub AddEntry()

Dim LR As Long, i As Long, cls
Dim LR2 As Long, j As Long, cls2

cls = Array("C2", "C3", "G2", "G3", "C5", "C6", "C7", "C8", "C9", "C10", "C11", "C12", "C13", "A17", "C17", "D17", "F17", "G17", "H17", "A18", "C18", "D18", "F18", "G18", "H18", "A19", "C19", "D19", "F19", "G19", "H19", "A20", "C20", "D20", "F20", "G20", "H20", "A21", "C21", "D21", "F21", "G21", "H21", "A25", "B25", "C25", "D25", "E25", "F25", "G25", "H25", "A26", "B26", "C26", "D26", "E26", "F26", "G26", "H26", "A27", "B27", "C27", "D27", "E27", "F27", "G27", "H27", "A28", "B28", "C28", "D28", "E28", "F28", "G28", "H28", "A32", "C32", "E32", "G32", "H32", "A33", "C33", "E33", "G33", "H33", "A34", "C34", "E34", "G34", "H34", "A35", "C35", "E35", "G35", "H35", "A39", "D39", "F39", "A40", "D40", "F40", "A41", "D41", "F41", "A45", "C45", "E45", "G45", "A46", "C46", "E46", "G46", "A47", "C47", "E47", "G47", "D51", "D52", "D53", "D54", "D55", "D56", "D57", "D58", "D59", "D60", "D61", "D62", "D63", "D64", "D65", "D66", "D67")
With Sheets("Tracker")
    LR = WorksheetFunction.Max(3, .Range("C" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
        .Cells(LR, i + 1).Value = Sheets("Form").Range(cls(i)).Value
    Next i
End With

cls2 = Array("E51", "E52", "E53", "E54", "E55", "E56", "E57", "E58", "G59", "E60", "E61", "E62", "G63", "E64", "E65", "E66", "E67", "C70", "D70", "E70", "F70", "G70", "H70", "C71", "E71", "G71", "C72", "E72", "G72", "C73", "E73", "G73", "C74", "E74", "G74", "C75", "E75", "G75", "C76", "E76", "G76", "C77", "E77", "G77", "C78", "E78", "G78", "C79", "E79", "G79", "C82", "D82", "E82", "F82", "G82", "H82", "C83", "E83", "G83", "C84", "E84", "G84", "B88", "B89", "B90", "B91", "C88", "C89", "C90", "C91", "D88", "D89", "D90", "D91", "E88", "E89", "E90", "E91", "F88", "F89", "F90", "F91", "G88", "G89", "G90", "G91", "H88", "H89", "H90", "H91")
With Sheets("Tracker")
    LR2 = WorksheetFunction.Max(3, .Range("EW" & Rows.Count).End(xlUp).Row + 1)
    For j = LBound(cls2) To UBound(cls2)
        .Cells(LR, j + 1).Value = Sheets("Form").Range(cls2(j)).Value
    Next j
End With
 
Last edited:
Deepak said:
Pls explain more! getting confused. Any visual sample!!
Click to expand...

Thanks Deepak, after your post I review the code again to explain in detail and think this could work let me try.

Code: 
.Cells(LR, i + [B1].Column).Value = Sheets("Form").Range(cls(i)).Value

and

Code: 
.Cells(LR2, j + [EO1].Column).Value = Sheets("Form").Range(cls2(j)).Value

Will inform if its not resolved... Thanks a lot for your consideration.
 
You must log in or register to reply here.
Back
Top