Posts by vradhak7

I got this awesome VBA which does the following from an VBA expert.

Is it possible to operate this code from module instead of pasting it in the source sheet. Also, it appears it is pasting all values greater than 60% also when i use this code. I want to restrict it to only <=60% fields.
Also when there is no value <=60% it has to exit macro and sloe the file. Is this possible?

My previous Message,

VBA Experts,
I have a source sheet and a destination sheet. In the "source" sheet, I have columns A to F. I want to filter column F and select anything <=60% and select the displayed range and paste as value in sheet "Destination".
After doing this, it has to go back to "Source sheet" and unfilter the selection. How can I do this using VBA? Your expertise is appreciated. I have attached the file for reference.

VBA code from the expert below:
--------------------------------------------------
Sub CopyData()
Dim wsSource As Worksheet, wsDest As Worksheet
Dim slr As Long, dlr As Long

Application.ScreenUpdating = False

Set wsSource = Sheets("Source")
Set wsDest = Sheets("Destination")

slr = wsSource.Cells(Rows.Count, 1).End(xlUp).Row

If slr < 4 Then
MsgBox "No data found on Source Sheet.", vbExclamation, "Data Not Found!"
Exit Sub
End If

dlr = wsDest.Cells(Rows.Count, 1).End(xlUp).Row
If dlr > 3 Then wsDest.Range("A3:F" & dlr).Clear

wsSource.AutoFilterMode = False

With wsSource.Rows(3)
.AutoFilter field:=6, Criteria1:="<=0.6"
wsSource.Range("A3:F" & slr).SpecialCells(xlCellTypeVisible).Copy
wsDest.Range("A3").PasteSpecial xlPasteValues
wsDest.Range("A3").PasteSpecial xlPasteFormats
.AutoFilter
End With

wsDest.Activate
Application.ScreenUpdating = True
End Sub

-------------------------------

Re: Filter Value from Column and paste selection as value in another Sheet VBA

My target is 60% always. Essentially, i want to open the file,click on a button and get the results in "Destination" sheet.

VBA Experts,
I have a source sheet and a destination sheet. In the "source" sheet, I have columns A to F. I want to filter column F and select anything <=60% and select the displayed range and paste as value in sheet "Destination".
After doing this, it has to go back to "Source sheet" and unfilter the selection. How can I do this using VBA? Your expertise is appreciated. I have attached the file for reference.

Re: Transpose column by date( Excel or VBA)

This is to be inserted in a module right ? The result is to populated in sheet "Output". I believe "Sheet1" IS "Output" here.

It gave an error in the below line:
vVolume = Sheets("Input").Range(Cells(CopyRow, StartColumn), Cells(CopyRow, StartColumn))

Hello Experts,
I have attached the file showing how my input looks like and how I would require my output. I could have more number of similar rows for different cities in column B and more date range and I would like to transpose it by date. Is there an efficient way to do this ? Your expertise would be very useful

Posted in vbaexpress:

http://www.vbaexpress.com/foru…y-date%28-Excel-or-VBA%29

forum.ozgrid.com/index.php?attachment/70736/

Re: Automatically move to next sheet and perform Macro

Yes, It works now!! I had earlier given it a different name.

One more question:
If I want to copy the values of the rows only..and paste it in sheet4 ...how do i do it?

The current progam copies the rows which also included its formula. I don't want that.

Re: Automatically move to next sheet and perform Macro

It is giving a "Subscript out of range" error

and is highlighting this part of the code (in italics) (Commented)

Sub test() 


Dim LR As Long 
Dim i As Long 


Dim sh As Variant 


For Each sh In Array(Sheet1, Sheet2, Sheet3) 
With sh 
LR = .Range("G" & Rows.Count).End(xlUp).Row 
For i = 1 To LR 
'// Original italicized text below
If .Range("G" & i).Value = 1 Then .Rows(i).Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)




Next i 
End With 
Next 


End Sub

[FONT=&quot]Hello Experts,

I have this VBA code.

I need this code to automatically perform it in the next sheets. (Sheet2 and Sheet3) and copy the results after the last used row of the target sheet (Sheet 4). How do I add that piece to this existing code ? Your inputs will be very useful.[/FONT]
[FONT=&quot]Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("G" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("G" & i).Value = 1 Then .Rows(i).Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

Also posted the same in:

http://www.vbaexpress.com/foru…t-sheet-and-perform-Macro

[/FONT]

Hello Experts,

I have this VBA code.

I need this code to automatically perform it in the next sheets. (Sheet2 and Sheet3) and copy the results after the last used row of the target sheet (Sheet 4). How do I add that piece to this existing code ? Your inputs will be very useful.

Sub test()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("G" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("G" & i).Value = 1 Then .Rows(i).Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

Also posted the same in:

http://www.vbaexpress.com/foru…t-sheet-and-perform-Macro

Re: Lookup value in column and get result from relevent page

@stephenR..Did you mean I need to reciprocate the answer in the other link ? Thanks!

Re: Lookup value in column and get result from relevent page

StephenR- Yes, the expected answer in the first row should be 50/345/568/46 .

Hello VBA Experts,
I have attached the file for reference.
The VBA tool has to lookup the value in column B and C in worksheet "Dashboard" from worksheet "Group1" and "Group2" depending on the values in column B and C.

There are some constraint which goes into finding the value:
We need values of first "Tuesday" only to be displayed in the worksheet "dashboard".
For values in column F of worksheet "Dashboard" the tool has to lookup value "100%" under column D( worksheet "Group1" and "Group2") corresponding to the value in column B and C of worksheet "Dashboard".
For values in column G of worksheet "Dashboard" the tool has to lookup value "Cat volume" under column D( worksheet "Group1" and "Group2") corresponding to the value in column B and C of worksheet "Dashboard".
For values in column I of worksheet "Dashboard" the tool has to lookup value "Variable volume" under column D( worksheet "Group1" and "Group2") corresponding to the value in column B and C of worksheet "Dashboard".
For values in column F of worksheet "Dashboard" the tool has to lookup value "Pub volume" under column D( worksheet "Group1" and "Group2") corresponding to the value in column B and C of worksheet "Dashboard".

Automating this process would be greatly appreciated!
Regards
vradhak7

Other forum posted:

http://www.vbaexpress.com/foru…-page&p=347185#post347185