• 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

vletm

Excel Ninja
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

Member
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

vletm

Excel Ninja
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

Member
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:

bijicha

Member
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

Member
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

Member
Hi Vletm,

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