How can I copy the range like ctrl+A in VBA

Question

I got a problem with copying the range of cells. Usually I used to make it with activecell method. But in current situation it doesn't work. I mean the code does not select the whole range of cells. How can I apply CTRL+A excel shortcut to VBA?

Sub MergeDifferentWorkbooksTogether()

Dim wbk As Workbook
Dim wbk1 As Workbook
Dim Filename As String
Dim Path As String
Dim D As Date
D = Date - 3

Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\xezer.suleymanov\Desktop\Summary & D"
Set wbk1 = Workbooks("Summary & D")

Path = "\\FILESRV\File Server\Hesabatliq\Umumi\Others\Branchs' TB\Branchs' TB as of  2018\" & D
Filename = Dir(Path & "\*.xlsx")

Do While Len(Filename) > 0
Set wbk = Workbooks.Open(Path & "\" & Filename)
wbk.Activate
Range("A6").Value = "Branch Name"
Range("B1").Copy
Range("B6").End(xlDown).Offset(0, -1).Activate
Range(ActiveCell, ActiveCell.End(xlUp).Offset(1, 0)).Select
Selection.PasteSpecial xlPasteAll
Application.CutCopyMode = False
Range("A6").Activate
Range(ActiveCell, ActiveCell.End(xlToRight).End(xlDown)).Copy
wbk1.Activate
Application.DisplayAlerts = False
wbk1.Sheets("Sheet1").Range("A1048576").End(xlUp).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteAll
wbk.Close True
Filename = Dir

Loop
End Sub

.Copy is the method to use on a range. And what do you mean by doesn't work? What is happening? Any error message? And where is wbk1 defined and set? — QHarr, Apr 23 '18 at 06:33
I edited full code. In fact VBA does not show any error to me. But when I check the file in the final step I see that the code has not copied full range of cells. — Khazar, Apr 23 '18 at 06:42
What do you get with Debug.Print Range(ActiveCell, ActiveCell.End(xlToRight).End(xlDown)).Address added immediately before wbk1.Activate ? — QHarr, Apr 23 '18 at 06:47
I didn't get debug. when I just check my final file I see that the code has not copied full range. for example from top to middle 50 rows has been copied but another 50 row has not been copied. That's why I think I have to use ctrl+a option. but I don't know apply it to VBA macro — Khazar, Apr 23 '18 at 06:53
xlToRight) and End(xlDown) will stop at first blank row or column. Do you have a blank row or column in the range? Have a look at https://www.rondebruin.nl/win/s9/win005.htm — QHarr, Apr 23 '18 at 06:55
I recommend to read and use [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to clean up your code, make it faster and more stable. Also you should specify a worksheet for every `Range()` like `wbk.Worksheets("MySheet").Range()` otherwise Excel guesses which sheet you mean and it might guess something different than you. — Pᴇʜ, Apr 23 '18 at 07:00

score 0 · Answer 1 · answered Apr 23 '18 at 07:37

You may try something like this...

Dim lr As Long, lc As Long
Dim rng As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(6, Columns.Count).End(xlToLeft).Column
Set rng = Range("A6", Cells(lr, lc))
rng.Copy

Also, if row5 is blank, you may also try...

Range("A6").CurrentRegion.Copy

How can I copy the range like ctrl+A in VBA

1 Answers1