copy sheet content to another sheet

Question

I want to copy the content of one sheet at the end of the other, i have tried this vba code and it works,

Private Sub CommandButton1_Click()
Sheets("B").Select
Range("A1:H14").Select
Range("A1:H14").Copy

Sheets("A").Select
' Find the last row of data
Range("B48:I48").Select
ActiveSheet.Paste
Sheets("A").Select
End Sub

but what i want is to copy without having to specify the range of the data, because i have many files and many data and it's gonna be hard to do all of that manually and change the range a each time.

Yes indeed, that would be hard. Therefore VBA offers the concept of variables. Learn about them here. http://www.vbtutor.net/lesson6.html or, perhaps, here http://stackoverflow.com/questions/12199318/vba-selecting-range-by-variables — Variatus, Apr 18 '17 at 09:38
If you do not specify the cells, which cells should be copied? Do you want to copy all cells to the other sheet? — Erdem Akkas, Apr 18 '17 at 09:39
@ErdemAkkas yes i want to copy all cells to the other sheet, and if i don't specify the range, it give me an error. — zenami, Apr 18 '17 at 09:41

score 4 · Answer 1 · answered Apr 18 '17 at 09:59

4

Below will copy entire content in Sheet B to Sheet A

Sheets("B").Cells.Copy Destination:=Sheets("A").Range("A1")

You do not need to select cells while copying.

answered Apr 18 '17 at 09:59

Erdem Akkas

2,062
10
15

1

This actually overwites the data that are in sheet(A) – zenami Apr 18 '17 at 13:48

gr8tech · Answer 2 · 2017-04-18T18:33:55.447

0

I usually do something like this, assuming sheet1 is the sheet to be updated by data from sheet2;

dim destLen as Long 'rows used in sheet 1
dim sourceLen as Long 'rows used in sheet 2

Open directory with source files and loop through each file and do the following

destLen = Sheet1.Range("A"&Rows.Count).End(xlUp).Row
sourceLen = Sheet2.Range("A"&Rows.Count).End(xlUp).Row
Sheet2.Range("B1" & ":I" & sourceLen).copy
Sheet1.Range("A" & destLen + 1).pasteSpecial xlValues
Application.CutCopyMode = False

edited Apr 18 '17 at 18:33

answered Apr 18 '17 at 09:52

gr8tech

174
1
7

True, my mistake, I have corrected. The idea was primary to show how to obtain the last row. There are alternatives like usedrange but I have found it to be a bit unpredectable and the above has worked out for me thus far – gr8tech Apr 18 '17 at 18:33

Shai Rado · Accepted Answer · 2017-04-18T13:58:31.093

There's no need to use so many Select, which slows down the code, you can use the 1 line below will copy the entire contents of Sheet("B") to the first empty row at Column "A" in Sheet("A").

Dim Rng             As Range
Dim lRow            As Long
Dim lCol            As Long
Dim lPasteRow       As Long

With Sheets("B")

    lRow = .Cells.Find(What:="*", _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

    lCol = .Cells.Find(What:="*", _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column

    lPasteRow = Sheets("A").Cells.Find(What:="*", _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row

    .Range(.Cells(1, 1), .Cells(lRow, lCol)).Copy Destination:=Sheets("A").Range("A" & lPasteRow + 1)
End With

this works like a charm, i have a question, what should i change to use a sheet from another file and copy it? — zenami, Apr 18 '17 at 14:06
i want to copy the content of sheet(1) from file1, to sheet(1) from file2, without overwriting the existent data — zenami, Apr 18 '17 at 14:14

copy sheet content to another sheet

3 Answers3

Linked