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,

Could pls help me with this and let me know that were i am going wrong.

I am calculating the sum from the column(TOTAL_DLR_AM) of the first table and pasting the calculated sum in the row named Avg of the table (Approvals Summary) . i am able to do this, but the problem is that its calculating up to the next row of the  table (calculation).

I want to calculate only the data from the first table of the column(TOTAL_DLR_AM) and not from the calculation table.

Below is the code:

 Windows("c250k.xlsm").Activate

    With Sheets("Sheet1")

    myRow = .Range("E" & .Rows.Count).End(xlUp).Row

    rSum = Application.WorksheetFunction.Match( _

    "Avg", .Columns(1), 0)

    .Cells(rSum, 1).Offset(, 1).Formula = "=SUM(E3:E" & myRow & ")"

    .Columns().AutoFit

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

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

        :=False, Transpose:=False

    Application.CutCopyMode = False

    End With

Thank You!

Regards

Wither

Answer 4

Hi,

data in activesheet

based on the below pic.

try this...

Sub Formula_Avg_Sum_Total()

'May 22, 2016

Dim ws As Worksheet

Set ws = ActiveSheet ' << or  Sheets("Sheet1")  / change as needed

Dim r1 As Long, c1 As Long, r As Long

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

c1 = ws.Range("A1").CurrentRegion.Columns.Count

r = ws.Cells(Rows.Count, 1).End(xlUp).Row

Dim sLet As String

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

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

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

ws.Cells(r, 2).Value = ws.Cells(r - 2, 2).Value + ws.Cells(r - 1, 2).Value

ws.Cells(r - 2, 2).Resize(3).NumberFormat = "0.00"

End Sub

Answer 5

Hello Tasosk,

Thanks for your reply ! The above code is working.

But when i run it in other worksheet its calculating the Column H (TAT) of the first table ,so how do i change it to column G.

I want to calculate the column G of the first table.

Also, can u pls tell what the below code means.

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 & ")"

Thanks & Regards

Wither