• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

bijicha

Member
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

75376
 

Attachments

  • STATISTICS - 2021.xlsm
    23.4 KB · Views: 2
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?
 
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
 

Attachments

  • report-2021.xls
    234.5 KB · Views: 1
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?
 
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 <<<
Code:
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
 
Last edited by a moderator:
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
 
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

Code:
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
 
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.
 

Attachments

  • STATISTICS - 2021.xlsm
    27.9 KB · Views: 3
Hi Vletm,

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