VBA code to 'Copy - Paste Special - Values' target date

[Marklarbear]

HI brains trust -

I have a a tab where I keep track of historical data for a group of activities (lets call it sports for this example). There are formulas that pull the relevant stats from other tabs. What I want to be able to do is enter in a target date (cellA2) and push a magic button with VBA code behind it that will go down the list of dates in column A and 'copy - paste special - values' for all rows that correspond to the target date.

In the image example - it would be rows 6, 15, 24, 33 that would be processed - all other rows would remain untouched. The VBA code needs to identify the date in column A and apply 'copy - paste special - values' for that row - not apply it every 9th row....

cheers
for your help

 

[vcoolio]

Hello Mark,

The VBA code needs to identify the date in column A and apply 'copy - paste special - values' for that row - not apply it every 9th row....

Are you saying that you need the the relevant rows converted to values only and not copy/pasted as values to another sheet?

Cheerio,
vcoolio.

 

[Marklarbear]

Hello Mark,


Are you saying that you need the the relevant rows converted to values only and not copy/pasted as values to another sheet?

Cheerio,
vcoolio.

ahh - yes that is correct....

 

[vcoolio]

Hello Mark,

Try the following code, placed in a standard module and assigned to a button:

Sub Test()

    Dim c As Range
    Dim lr As Long
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
    
Application.ScreenUpdating = False

    For Each c In Sheet1.Range("A6:A" & lr)
        If c.Value = Sheet1.[A2].Value Then
              With Range(Cells(c.Row, "B"), Cells(c.Row, "E")) '---->You may have to change the "E" to whichever column is your last column.
                   .Value = .Value
              End With
        End If
    Next c

Application.ScreenUpdating = True

End Sub

I've used the sheet code (Sheet1) above which could(?) be your sheet code. If not, change it to suit or declare a sheet variable and use your sheet name instead.

I hope that this helps.

Cheerio,
vcoolio.

 

[Marklarbear]

Hello Mark,

Try the following code, placed in a standard module and assigned to a button:

Sub Test()

    Dim c As Range
    Dim lr As Long
    lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
   
Application.ScreenUpdating = False

    For Each c In Sheet1.Range("A6:A" & lr)
        If c.Value = Sheet1.[A2].Value Then
              With Range(Cells(c.Row, "B"), Cells(c.Row, "E")) '---->You may have to change the "E" to whichever column is your last column.
                   .Value = .Value
              End With
        End If
    Next c

Application.ScreenUpdating = True

End Sub

I've used the sheet code (Sheet1) above which could(?) be your sheet code. If not, change it to suit or declare a sheet variable and use your sheet name instead.

I hope that this helps.

Cheerio,
vcoolio.

cheers Vcoolio.... but i'm not quite getting there... it only seems to convert to values for the first instance of the date in the range (row 6). the only change i have made is the 3 spots referring to the sheet name (changed from Sheet1 to Sheet5). Even if I change the date in cell A2, it only converts to values row 6.

Mark

 

[vcoolio]

Hello Mark,

the only change i have made is the 3 spots referring to the sheet name (changed from Sheet1 to Sheet5).

I reckon the problem lies here. I've used the sheet code in the VBA script (Sheet1) not the sheet name. I believe that Sheet5 is your sheet name so check in the VBA Editor the sheet code for Sheet5. In the VBA Editor, over to the left in the Project Explorer, you'll see all your sheet names in parentheses with the sheet codes immediately to the left of the sheet names.

I created a quick mock-up of your workbook based on the image you supplied with some very basic formulae included in all columns and the code works exactly as it should regardless of the chosen date. You'll find the sample. See here.
Have a play with the sample to see how it works.

Let me know how it goes.

Cheerio,
vcoolio.

 

[Marklarbear]

Hello Mark,


I reckon the problem lies here. I've used the sheet code in the VBA script (Sheet1) not the sheet name. I believe that Sheet5 is your sheet name so check in the VBA Editor the sheet code for Sheet5. In the VBA Editor, over to the left in the Project Explorer, you'll see all your sheet names in parentheses with the sheet codes immediately to the left of the sheet names.

I created a quick mock-up of your workbook based on the image you supplied with some very basic formulae included in all columns and the code works exactly as it should regardless of the chosen date. You'll find the sample. See here.
Have a play with the sample to see how it works.

Let me know how it goes.

Cheerio,
vcoolio.

makes sense (i'm unable to open your sample due to work security settings) - i have updated the code in the 3 spots from Sheet1 to Sheets("Sheet5") and that seems to work perfectly.... woo hoo!! in the test data.

now in the actual data that i need to apply it to - I have 4800 rows of data (this will grow ongoing). When i run the script it works the way it is designed to however it is extremely resource intensive (to the point where i am unable to do anything else) and takes just over 7 minutes to complete. is there a way to speed the process up?

In my limited knowledge.... would the process be speed up if it applied a filter to only show the target date and then apply the conversion steps to those visible rows only (so that it doesnt need to go thru all 4800 rows of data?)

In my case this would mean going thru 33 rows of data (only the visible filtered rows) and converting rather than 4800 rows of data and converting only the applicable rows?

 

[vcoolio]

Hello Mark,

That would be a good idea. You can automate the filter if you like. In the same module wherein you placed the "Test" code, just below it, paste this code:-

Sub Test2()

    Dim dt As String
    dt = Sheet1.Range("A2").Value
   
    Application.ScreenUpdating = False
   
    With Sheet1.[A5].CurrentRegion
           .AutoFilter 1, dt, 7
                Test
           .AutoFilter
    End With
   
    Application.ScreenUpdating = True

End Sub

This "Test2" code calls the "Test" code. You'll find it all much quicker. I expanded my test sample to 6500 rows and the result was instant. Again, you'll just need to change the sheet references to suit.
Assign the "Test2" code to the button instead of the "Test" code.

I hope that this helps.

Cheerio,
vcoolio.

 

[Marklarbear]

Hello Mark,

That would be a good idea. You can automate the filter if you like. In the same module wherein you placed the "Test" code, just below it, paste this code:-

Sub Test2()

    Dim dt As String
    dt = Sheet1.Range("A2").Value
  
    Application.ScreenUpdating = False
  
    With Sheet1.[A5].CurrentRegion
           .AutoFilter 1, dt, 7
                Test
           .AutoFilter
    End With
  
    Application.ScreenUpdating = True

End Sub

This "Test2" code calls the "Test" code. You'll find it all much quicker. I expanded my test sample to 6500 rows and the result was instant. Again, you'll just need to change the sheet references to suit.
Assign the "Test2" code to the button instead of the "Test" code.

I hope that this helps.

Cheerio,
vcoolio.

hey vcoolio

Apologies for delay in replying... got pulled away to another project and now back to this one...

I seem to be having an issue in the Test2 code with the Autofilter when I run it (see screen shot).. I have assigned Test2 to the button but it fails to filter.... where abouts have I broken it??

 

[vcoolio]

Hello Mark,

I've had a quick look at your predicament and I think that the problem may be with the use of the sheet reference. In the Save_Actuals code, you're using "Sheet5" and "Sheets5". The Test2 code is referencing "Sheet5". Are you using the sheet name or the sheet code in both? If you want to use the sheet name, the reference in the code needs to be as follows:
Sheets("Sheet5")
The sheet code should be referenced just as Sheet5. Check your Project Explorer in the VBA Editor.

The link to the sample file has long expired and I don't have a copy to test with. Consider using the Mr Excel XL2BB sample sheet uploader or a file sharing site such as Drop Box or WeTransfer to upload a sample of your file which we can work and test with.

Cheerio,
vcoolio.