Copy data to other sheet

Chrismitchell23

New Member
Joined
Dec 18, 2024
Messages
2
Office Version
  1. 2024
Platform
  1. Windows
Hello,

I need some assistance with a code to copy some data in a column, then paste special 123 in another sheet, but into the correct date column
The information here is based on counta from a master data sheet
1734513459320.png


I need to copy the values from column K, rows 2-34 into another sheet called Master Data but the values need to be pasted into the column matching the date in K1

1734513565399.png



EDIT:
The report is refreshed weekly and the data in the columns needs to remain in place. Then the next week will be the following Friday after the date in K1
 
Last edited by a moderator:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
See if this works for you.

VBA Code:
Sub CopyCurrentToMaster()

    Dim shtCurrent As Worksheet, shtMstr As Worksheet
    Dim rngCurrent As Range, rngMstr As Range
    Dim colMstr As Long, rowMstr As Long
    
    Set shtCurrent = ActiveSheet                    ' ideally change to the sheet name using Worksheets("Sheet_Name")
    Set shtMstr = Worksheets("Master Data")
    
    Set rngCurrent = shtCurrent.Range("K2:K34")
    
    ' Get column in master based on week
    With Application
        colMstr = .IfError(.Match(shtCurrent.Range("K1").Value2, shtMstr.Rows(1), 0), 0)
    End With
    
    rowMstr = 3                                     ' 1st Row for output
    If colMstr = 0 Then
        MsgBox "Date not found in Master (" & shtCurrent.Range("K1").Value & ")"
        Exit Sub
    End If

    With rngCurrent
        shtMstr.Cells(rowMstr, colMstr).Resize(.Rows.Count, 1).Value = .Value
    End With

End Sub
 
Upvote 0
Solution
See if this works for you.

VBA Code:
Sub CopyCurrentToMaster()

    Dim shtCurrent As Worksheet, shtMstr As Worksheet
    Dim rngCurrent As Range, rngMstr As Range
    Dim colMstr As Long, rowMstr As Long
   
    Set shtCurrent = ActiveSheet                    ' ideally change to the sheet name using Worksheets("Sheet_Name")
    Set shtMstr = Worksheets("Master Data")
   
    Set rngCurrent = shtCurrent.Range("K2:K34")
   
    ' Get column in master based on week
    With Application
        colMstr = .IfError(.Match(shtCurrent.Range("K1").Value2, shtMstr.Rows(1), 0), 0)
    End With
   
    rowMstr = 3                                     ' 1st Row for output
    If colMstr = 0 Then
        MsgBox "Date not found in Master (" & shtCurrent.Range("K1").Value & ")"
        Exit Sub
    End If

    With rngCurrent
        shtMstr.Cells(rowMstr, colMstr).Resize(.Rows.Count, 1).Value = .Value
    End With

End Sub
That works perfectly, you're an actual legend

I had to change from using date to "week No. + Year", it didn't like the date for some reason. Entered the bits as you recommended and now works from the main page :)

Life saver, thank you
 
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top