0

I want to copy a range from sheet "Data" to the sheet "Sheet1". However, only one cell is being copied to sheet1. Why is it so?

Application.DisplayAlerts = False
Sheets("Data").Range("A1:B1").Copy Destination:=Sheets("Sheet1").Range("$A$1")
Sheets("Data").Delete
Sheets("Sheet1").Select
Gass
  • 7,536
  • 3
  • 37
  • 41
Chewjunnie
  • 137
  • 6
  • 1
    Nothing wrong with the code. Are you sure both cells (A1 and B1) have data? – Siddharth Rout Feb 22 '21 at 06:03
  • there is data in Data sheet for A1, but for col b the data only starts at B3 – Chewjunnie Feb 22 '21 at 06:07
  • In that case your should specify a range that is larger than A1:B1, perhaps A1:B10. To copy Range("A:B") can't be recommended because there are more than 2 million cells in that range and it's unlikely that you want to copy so many. – Variatus Feb 22 '21 at 06:12
  • 1
    **1.** Find the last row of Col B as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) **2.** Construct your range For example `Set Rng = Sheets("Data").Range("A1:B" & LastRow)` and then copy the range. `rng.Copy Destination:=Sheets("Sheet1").Range("$A$1")` – Siddharth Rout Feb 22 '21 at 06:14
  • It work! Can i ask why must i declare b range? Why cant i use Range("A1:B1") instead? – Chewjunnie Feb 22 '21 at 06:35
  • `A1:B1` will only copy two cells `A1` and `B1`. Whereas using `"A1:B" & LastRow` will copy from cell `A1` to last row of column `B` – Siddharth Rout Feb 22 '21 at 06:37
  • Noted! Thank you! – Chewjunnie Feb 22 '21 at 06:39

1 Answers1

0

Some suggestions:

Application.DisplayAlerts = False

You have deactivated the display alerts application in Excel. This means it will not work anymore unless you restart Excel or activated again before the sub method ends. Like this:

Application.DisplayAlerts = True

It's not necessary for your code, due to the simplicity of it. I would deactivate some applications in complex macros where the application could stop it or slow it down.

When working with ranges, you can build it by finding where it starts and where it ends (rows and columns).

Sub move_data()

Dim col_start As Long
Dim last_col As Long
Dim start_row As Long
Dim last_row As Long

'CONFIG
col_start = 2 'set the column where the range starts

With Sheets("Data")

'firstly select the sheet you are working with
'(this is necessary for copying)
Sheets("Data").Select

'this will return the row number for the first cell with content
'moving from top to bottom
start_row = .Cells(1, col_start).End(xlDown).Row

'is the same logic but know instead of cells been in row 1
'rows.count will return the total number of rows in Excel
'so it will explore from bottom up until it finds something.
last_row = .Cells(Rows.Count, col_start).End(xlUp).Row

'now is the same logic, but with columns
last_col = .Cells(start_row, Columns.Count).End(xlToLeft).Column

'so finally you have all the information you need
'to build your range, and it is not hardcoded so
'it will work everytime for different ranges

'this is your range
.Range(Cells(start_row, col_start), Cells(last_row, last_col)).Copy

End With

'now transfer to your target sheet
Sheets("Sheet1").Range("A1").PasteSpecial

End Sub

Here you can see it in action:

enter image description here

Gass
  • 7,536
  • 3
  • 37
  • 41