Copy specific cells based on input value in another sheet, and paste values to another sheet via VBA

[Machl22]

Hello, I have an area of calculated values for 7 areas in columns from V7:AC7 on sheet "LastMilePOBoxSummary."

U7 is "Week" with the corresponding weeks of the year listed in each row. So, each RC contains the Last Mile % data point for each area for each week.

I want to, using the user-inputted "Week" value in F1 on "Summary" sheet, select all of the cells from U:AC that correspond to that user-inputted week, and copy those values onto "LastMilePOBoxSummary_Tracking." I will have the same column headers in row 1 on "LastMilePOBoxSummary_Tracking," thus the paste should begin on the next available row.

Any ideas are greatly appreciated!

~Michael

 

[JLGWhiz]

This is based on my intepretation of your sheet layout, so it might need some tweaking if I guessed wrong.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("LastMilePOBosSummary")
Set sh2 = Sheets("LastMilePOBoxSummary_Tracking")
    With sh1
        Intersect(.Range("U:AC"), .UsedRange.Offset(5)).AutoFilter 1, .Range("F1").Value
        Intersect(.Range("U:AC"), .UsedRange.Offset(6)).Copy sh2.Cells(Rows.Count, "U").End(xlUp)(2)
        .AutoFilterMode = False
    End With
End Sub

 

[Machl22]

Thank you so much for your response, JLGWhiz! One thing, the "F1" value (corresponding to week of the year) is located on "Summary" tab, and I want to use that value, to make the selection of cells in U:AC on "LastMilePOBoxSummary", where U corresponds to the week that matches the "F1" value on "Summary", and paste those values onto "LastMilePOBoxSummary_Tracking." In your code, would I have to include and dim a 3rd worksheet?

Perhaps similar to?

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Set sh1 = Sheets("LastMilePOBosSummary")
Set sh2 = Sheets("LastMilePOBoxSummary_Tracking")
Set sh3 = Sheets("Summary")
With sh3
Intersect.sh1(.Range("U:AC"), .UsedRange.Offset(5)).AutoFilter 1, .Range("F1").Value
Intersect(.Range("U:AC"), .UsedRange.Offset(6)).Copy sh2.Cells(Rows.Count, "U").End(xlUp)(2)
.AutoFilterMode = False
End With
End Sub



Thank you very much for your help!

 

[JLGWhiz]

More like this

Sub t()
 Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
 Set sh1 = Sheets("LastMilePOBosSummary")
 Set sh2 = Sheets("LastMilePOBoxSummary_Tracking")
 Set sh3 = Sheets("Summary")
     With sh1
         Intersect(.Range("U:AC"), .UsedRange.Offset(5)).AutoFilter 1, sh3.Range("F1").Value
         Intersect(.Range("U:AC"), .UsedRange.Offset(6)).Copy sh2.Cells(Rows.Count, "U").End(xlUp)(2)
         AutoFilterMode = False
     End With
 End Sub

When posting code. Select the code by highlighting with the mouse pointer, then click the (#) symbol on the tool bar of the reply pane. That will put code tags on the code and display it in format.

 

[Machl22]

Hi, JLGWhiz! Thank you again for your response. We are getting closer. The revised code you provided correctly filters the correct range of cells in "LastMilePOBoxSummary" based on the input value in "F1" on "Summary," but it didn't appear that the resultant cells were copied and pasted into the next available row on "LastMilePOBoxSummary_Tracking." Also, the filter remains on after running the macro. Is there a way to remove the filter after the copy and paste values onto "LastMilePOBoxSummary_Tracking," and return to "Summary?"

Thank you SO much for all of your help!

 

[JLGWhiz]

Hi, JLGWhiz! Thank you again for your response. We are getting closer. The revised code you provided correctly filters the correct range of cells in "LastMilePOBoxSummary" based on the input value in "F1" on "Summary," but it didn't appear that the resultant cells were copied and pasted into the next available row on "LastMilePOBoxSummary_Tracking." Also, the filter remains on after running the macro. Is there a way to remove the filter after the copy and paste values onto "LastMilePOBoxSummary_Tracking," and return to "Summary?"

Thank you SO much for all of your help!

This line needs a period in front of it

.AutoFilterMode = False

To make it turn the filter off.

In my test set up. It copies the the range from sh1 to the same columns on sh2. I assumed by your statement that you had the same column headers in both sheets that they would be in the same columns. Did I guess wrong?

 

[Machl22]

Hi JLGWhiz! Thank you so much for all of your help! I believe I have it dialed-in now. I'm very thankful, and hope you have nice evening

 

[JLGWhiz]

You are welcome,
regards, JLG