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

Hello Tasosk,

Thank you for explaining it and the code works.

Regards

Wither