Automatically populate columns on one sheet from another sheet

Question

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

I tried using =sheet1!A1 , but it only returns the value 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 contain 500 or 1000 rows (I fill sheet1 from my database)). How to use some of these columns on another sheet showing all 500 or 1000 rows?

Answer 1

Below, the code will search for the last used row in sheet1 and copy the entire range from A1 to the last used row in column A in Sheet2 in the same place.

 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 

Answer 2

I used in Google Sheets

  ={sheetname!columnnamefrom:columnnameto} 

Example:

• ={sheet1!A:A}
• ={sheet2!A4:A20}

Answer 3

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

This may not be the best way, but you can enter the following

= IF (A1 & sheet1 l ;!> "!", Sheet1 A1 "")

and drag it to the maximum number of expected rows.

Answer 4

Use the EntireColumn property for what it is intended. C #, but should give you a good idea on how to do this:

 string rangeQuery = "A1:A1"; Range range = workSheet.get_Range(rangeQuery, Type.Missing); range = range.EntireColumn; 

Answer 5

In Google Sheets you can use = ArrayFormula (Sheet1! B2: B) in the first cell and fill the entire contents of the column, not sure if this will work in excel