Auto populate columns in one sheet from another sheet

9
votes

I would like to populate columns in sheet2 from sheet1. If I have column A in Sheet1 I want A in Sheet2 to have the same information.

I tried using =sheet1!A1 but it only returns the value from A1 in sheet1. I tried using =sheet1!A but it only returns #NAME?.

If Column A from Sheet1 has a dynamic range (it can be empty or have 500 or 1000 rows (I'm populating sheet1 from my database)). How do I use some of those columns in another sheet showing all 500 or 1000 rows?

5
excelexcel-2010vba
-sheet1!a1 will work. put it into A1 onsheet to then copy/paste the formula down and excel will auto-increment the forumla. If you are wanting to process the entire column at once with VBA, there is an answer below that covers that. - Alan Waage

5 Answers

6
votes

If I understood you right you want to have sheet1!A1 in sheet2!A1, sheet1!A2 in sheet2!A2,...right?

It might not be the best way but you may type the following

=IF(sheet1!A1<>"",sheet1!A1,"")

and drag it down to the maximum number of rows you expect.

5
votes

I have used in Google Sheets

 ={sheetname!columnnamefrom:columnnameto}

Example:

  1. ={sheet1!A:A}
  2. ={sheet2!A4:A20}
4
votes

Below code will look for last used row in sheet1 and copy the entire range from A1 upto last used row in column A to Sheet2 at exact same location.

Sub test()

    Dim lastRow As Long
    lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet2").Range("A1:A" & lastRow).Value = Sheets("Sheet1").Range("A1:A" & lastRow).Value

End Sub
2
votes

In Google Sheets you can use =ArrayFormula(Sheet1!B2:B)on the first cell and it will populate all column contents not sure if that will work in excel

0
votes

Use the 'EntireColumn' property, that's what it is there for. C# snippet, but should give you a good indication of how to do this:

string rangeQuery = "A1:A1";

Range range = workSheet.get_Range(rangeQuery, Type.Missing);

range = range.EntireColumn;