VBA help required.

[Jagdev Singh]

Hi Experts

I recorded the attached macro and it is working fine. Could you please help me to make it more precise. I am pulling data from specific cells from sheet1 to sheet2. To make it workable I have to hide few column because of the input cell punch the data on the respective cell in the sheet2. Could you please help me with the code which will place the paste data in a sequence one after the other and avoid blank columns.

Regards,

JD

 

[Marc L]

Hi !

Just hide columns before a global Range.Copy which does not include
hidden cells …
​

 

[Jagdev Singh]

Hi Marc

Thanks for your view.

I somehow manage to get deal with it, with the help of the below code. Pasting it here and hope it will help others.

Sub copypaste()
Dim LR As Long, i As Long, cls
cls = Array("C5", "C7", "C9", "C11", "C13", "C15", "C17", "C19", "C21")
With Sheets("Sheet2")
    LR = WorksheetFunction.Max(1, .Range("A" & Rows.Count).End(xlUp).Row + 1)
    For i = LBound(cls) To UBound(cls)
        ActiveSheet.Range(cls(i)).Copy Destination:=.Cells(LR, i + 1)
    Next i
End With
 

End Sub

 

[Missi]

Hi All,
Can anyone please help on below request?
How to delete multiple columns in a workbook using header in VBA??
Supoose, I have 10 cols, say a,b,c,d,e,f,g,h,i,j. Here I want to retrieve cols c,e,h & j. How to do this??

Thanks in Adv.

 

[Jagdev Singh]

Hi Missi

I am bit unclear with your requirement. What if you can copy specific header column from the range above to a new sheet

While that solve your problem.

Regards,
JD

 

[Missi]

Hi Missi

I am bit unclear with your requirement. What if you can copy specific header column from the range above to a new sheet

While that solve your problem.

Regards,
JD

Thanks JD for this quick turn around! Appreciate your response but I resolved this issue.

But now I have another set of task. Using macro, can we select UNIQUE PROJECT ID once with SUM of its Cols separately?
Ex: My final content should be Unique Project ID once with SUM of all value in LAst 3 cols. Whenever, I select unique ID, It should fetch SUM of all corresponding values.


Below is Table details -
PROJECT ID PROJECT NAME YTD ACTUALS Jan 2015 Feb 2015
111111 ABC 248.17 52.34 99.50
111111 ABC 0.00 0.00 0.00
222222 XYZ 0.00 0.00 0.00
222222 XYZ 44.08 15.67 14.31
222222 XYZ 0.00 0.00 0.00
Total 292.25 68.00 113.82

 

[Jagdev Singh]

Hi Messi

You can easily achieve this with the following formula - \

=IF(MATCH(B8,A2:A6,0),SUM(C2:E2),"")

The value I added in Column B8 is 11111 and I get the desire result.

Regards,
JD