VBA Macro : Copy all the data from sheet 1 and paste it into the sheet 2

Question

Hello,

i am creating a  macro that would copy all the data from sheet 1 and paste it below the blank row of sheet 2 . 

                                Sheet 1 has 2 tables calculation and Approvals Summary. Below is the pic of Sheet1

                 Sheet 2 has some data in it. Below is the pic of sheet 2

I want to copy both the tables from sheet 1 and paste it below the blank row of sheet2. 

So ,I tried to copy all the data in sheet 1 by using Cells.Select . I am able to copy it but not paste the data in sheet 2.

Its gives an run time error 1004: To paste all cells from an excel worksheet into the current worksheet ,you must paste into the first cell.

Below is the code:

    Worksheets("Sheet1").Select

    Cells.Select

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False 

    Sheet2.Activate

    erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    Sheet2.Paste Destination:=Worksheets("Sheet2").Rows(erow)

    Application.CutCopyMode = False

So how do i copy the 2 tables from sheet 1 and paste it below the empty row of sheet 2 ?

Thanks in Advance!

Regards

Wither

Answer 1

Hi,

since the desired column is not the last

replace

sLet = UCase(Left$(ws.Cells(1, c1).Address(False, False), Len(ws.Cells(1, c1).Address(False, False)) - 1))

ws.Cells(r - 1, 2).Formula = "=SUM(" & sLet & "2:" & sLet & r1 & ")"

with

sLet="G"

XXXXXXXXXXXXX

your question

in vba

MsgBox Cells(1, 1).Address(False, False) >>> returns A1

MsgBox Cells(1, 27).Address(False, False) >>> returns AA1

 I need only the letter A or AA

so

UCase(left([A1], length[A1] - one))

or

left([A1], length[A1] - one)

returns to letter A (capital)

symbolic: [A1] is [Cells(1, 1).Address(False, False) ]

LCase (left([A1], length[A1] - one) returns to letter a (small)

xxxxxxxxxxxx

sample vba

MsgBox LCase(Left$(Cells(1, 27).Address(False, False), Len(Cells(1, 27).Address(False, False)) - 1))

returns aa

MsgBox UCase(Left$(Cells(1, 27).Address(False, False), Len(Cells(1, 27).Address(False, False)) - 1))

returns AA

also,

without UCase

MsgBox Left$(Cells(1, 27).Address(False, False), Len(Cells(1, 27).Address(False, False)) - 1)

returns AA

note

try  with .....

Cells(1, 1).Address(False, True)

Cells(1, 1).Address(True, False)

Cells(1, 1).Address(True, True)

Answer 2

Hi,

make a copy before...

[edit..]

Sub Append_Sht1_In_Sht2()

'May 22, 2016

Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")

Set ws2 = Sheets("Sheet2")

Dim r1 As Long, c1 As Long, r2 As Long

r1 = ws1.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

c1 = ws1.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

r2 = ws2.Range("A1").CurrentRegion.Rows.Count

ws1.Range("A1").Resize(r1, c1).Copy ws2.Cells(r2 + 2, 1)

ws2.UsedRange.EntireColumn.AutoFit

End Sub

Answer 3

Hi,

try this code

expected results in a new sheet

Sub Append_TwoSheets()

'May 20, 2016

Dim ws1 As Worksheet, ws2 As Worksheet, newws As Worksheet

Set ws1 = Sheets("Sheet1")

Set ws2 = Sheets("Sheet2")

Dim r1 As Long, r2 As Long, c As Long

r1 = ws1.Cells(1, 1).End(xlDown).Row

r2 = ws2.Range("A1").CurrentRegion.Rows.Count

c = ws2.Range("A1").CurrentRegion.Columns.Count

Set newws = Sheets.Add(before:=Sheets(1))

ws2.Range("A1").CurrentRegion.Copy Cells(1, 1)

ws1.Cells(3, 1).Resize(r1 - 2, c).Copy Cells(r2 + 1, 1)

r1 = Range("A1").CurrentRegion.Rows.Count

Dim sLet As String

sLet = UCase(Left$(Cells(1, c).Address(False, False), Len(Cells(1, c).Address(False, False)) - 1))

Cells(r1 + 2, 1).Resize(2) = Application.Transpose(Array("Avg", "Sum"))

Cells(r1 + 2, 2).Formula = "=AVERAGE(" & sLet & "2:" & sLet & r1 & ")"

Cells(r1 + 2, 2).NumberFormat = "0.000"

Cells(r1 + 3, 2).Formula = "=SUM(" & sLet & "2:" & sLet & r1 & ")"

ActiveSheet.UsedRange.EntireColumn.AutoFit

End Sub

Answer 4

Hello Tasosk,

Thanks for your reply!

I tried it but it is pasting both the sheet data together  and for Approvals Summary only the avg and sum is pasted not the entire table.  Below is the pic.

Want i want to do is copy both the tables (calculation and Approvals Summary) from sheet 1 and paste it below the first blank row of sheet2.   Below is the pic of the result which i am asking for.

but how do i do this ?

Regards

Wither

Answer 5

Hello Tasosk,

Its working perfectly ! Thank you so much (:-) (:-)

Regards

Wither