Populate data to few cells of active file, from another excel file

[bijicha]

Hi friends,

Can you help me to make a macro, to populate rows 5,6,7,8 and 9 data from another open file, but based on the cell selection on row three

as below, if i run the macro when the cell O3 selected then data to copied to O5, O6, O7,O8 ans O9, same way, if the selected cell is P3, then data to populate on P5, P6,P7,P8 and P9

I am doing this daily for every month as each sheet. Please help me with solution

 

[vletm]

bijicha
Without that other open file, it would be some challenges to create any code...
... hmm?
... of course, it would be possible, but then others should guess - what kind of file that would be?

 

[bijicha]

Hi,
Yes off course, its a file shared by another department with values summarized.. So my regular requirement is just select the day and run the macro, to populate that 5 values in same order..

Thanks

 

[vletm]

bijicha
Your previous file has two sheets.
... from which sheet that copy & paste should do?
... do those sheets always have same names?
... do those sheets always have same layout?
... how You would handle case, if there are also another Excel-files open?
eg above means ... that need to be sure about source?

 

[bijicha]

Hi Vletm,

I have a folder with the Statistics 2021 file with each month as separate sheet and each day 3d row as the picture showed in my initial post.
Daily i get a report from another team which is named report-2021, with two sheets, and the data they sharing on the sheet1, i need to populate to statistics 2021 file. rows 5 to 9. this is daily copy and paste.

I am thinking a macro, in statistics 2021, when i run it keeping the day cell selected, then populate the data to that column,rows from 5 to 9.

If the selected cell (P3) is 15th, then the macro to populate the data from P5 to P9, if the selected cell is 25 (Z3), then the macro to populate Z5 to Z9

I tried the below code to get the cell reference , but not succeeded

>>> use code - tags <<<

Windows("STATISTICS - 2021.xlsm").Activate
   
    Dim selcel As String
    Set selcell = Application.ActiveCell
    MsgBox selcell.Row
    MsgBox selcell.Column
   
    Range(selcell.Column & "5").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R1C1"
    Range(selcell.Column & "6").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R2C1"
    Range(selcell.Column & "7").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R3C1"
    Range(selcell.Column & "8").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R4C1"
    Range(selcell.Column & "9").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R5C1"
   
    Range(selcell.Column & "5:" selcell.Column & "9").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("O3").Select

 

[bijicha]

Hi Vletm,

also answer to your above questions

... from report 2021 ->sheet1 to Statistics 2021 -> July -> rows 5 to 9 (this depend on the month sheet and day)
... report 2021 have the same sheet names, but Statistics 2021 have each month new sheet
... yes
... need to work only on report 2021.csv and Statistics 2021, need to ignore all the other open excels

Thanks

 

[bijicha]

Hi Vletm,

I just got the desired result, ut not sure , what i did is right with the below code.. when i am running the macro selecting the day, same column 5 to 9 rows is getting populated

Windows("STATISTICS - 2021.xlsm").Activate
  
    Dim selcel As String
    Set selcell = Application.ActiveCell
    colname = Split(Cells(, selcell + 1).Address, "$")(1)
  
    Range(colname & "5").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R1C1"
    Range(colname & "6").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R2C1"
    Range(colname & "7").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R3C1"
    Range(colname & "8").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R4C1"
    Range(colname & "9").Select
    ActiveCell.FormulaR1C1 = "='[report-20.csv]Sheet1'!R5C1"
  
    Range(colname & "5:" & colname & "9").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("O3").Select

 

[vletm]

bijicha
I could check later this ...
Now, it seems that You could continue doing this manually for safety reasons.

 

[bijicha]

Hi Vletm,

Yes, i can.. Let me know your expert advice later

Thanks

 

[vletm]

bijicha
Your wrote based on the cell selection on row three
... then ...
One sample
how You could ...
Do Your selection on row three
to do Your daily duty.

 

[bijicha]

Hi Vletm,

Yes, its working.. and color coding and the msgox seems good for identifying the right column is selecting... Thanks for the support.