VBA code to do this special scenario

[PrashanthGM12345]

Hi i have excel workbook with 2 sheets.
I have attached the excel file for the reference.

I want to write a vba code to scan the month in cell C2 under "sheet 1" and to compare the months which are there in row 1 under "sheet 2" one by one
If sheet 1 month and sheet 2 month matches then, it should copy the audit score from sheet 1 to sheet 2 under the respecting months column.

Can please anyone help me with this?

 

[Hui]

Try this:

Sub Shift_Scores()

Dim c As Range

Sheet2.Activate

For Each c In Sheet2.Range("B1:M1")
  If c = Sheet1.Range("c2") Then
    c.Offset(1, 0).Resize(4, 1).Value = Sheet1.Range("F4:F7").Value
    Sheet1.Range("F4:F7").ClearContents
  End If
Next c

End Sub

 

[Marc L]

Hi !

I want to write a vba code to scan the month in cell C2 under "sheet 1" and to compare the months which are there in row 1 under "sheet 2"

To write your VBA code, you can start with MATCH worksheet function
(see its Excel help and try it within a cell) or Find method (see VBA help)
in order to locate directly at once the month column,
so no need to scan « one by one » …
​

 

[Hui]

although this maybe a better solution

Sub Shift_Scores()
Dim c As Integer

Sheet2.Activate

c = Sheet2.Range("B1:M1").Find(Sheet1.Range("c2")).Column
Sheet2.Range(Cells(2, c), Cells(5, c)).Value = Sheet1.Range("F4:F7").Value
Sheet1.Range("F4:F7").ClearContents

End Sub

 

[PrashanthGM12345]

Try this:

Sub Shift_Scores()

Dim c As Range

Sheet2.Activate

For Each c In Sheet2.Range("B1:M1")
  If c = Sheet1.Range("c2") Then
    c.Offset(1, 0).Resize(4, 1).Value = Sheet1.Range("F4:F7").Value
    Sheet1.Range("F4:F7").ClearContents
  End If
Next c

End Sub

Actually its moving the data from sheet 1 to sheet 2. I want it to copy the data from sheet 1 to sheet 2

 

[Hui]

It was copying the data
then clearing the sheet1 cells

The following copies the data

Sub Shift_Scores()
Dim c As Variant
Sheet2.Activate

Set c = Range("B1:M1").Find(Sheet1.Range("c2"))

c.Offset(1, 0).Resize(4, 1) = Sheet1.Range("F4:F7").Value

End Sub