Trying to get macro to copy and paste from sheet 1 to sheet 2 and delete contents from sheet 1

[shawn2209]

Hello,

I am still a beginner to macros and learn quickly by reading forums on other's experiences with running macros. So let me explain what I am trying to do and I believe I am failing miserably. I am wanting to get the macro to copy the date from sheet 1 and paste it is cell A1 on sheet two and then take the information from selected cells on sheet 1 and past to sheet 2. At the same time I am wanting to erase the data from sheet 1 to where I can add more information on that sheet and then once I run the macro again, it copies the same data and paste it into sheet two at the next empty cell. I am wanting to keep a running list of inventory at my work with adding the inventory information for each "tote" and copy it into a running sheet (sheet 2). The issue is once I finish with the inventory in "tote #1", I am wanting the macro to erase the data to start the inventory list for "tote" #2 and the paste it onto sheet 2 on the next empty cell without deleting the information for "tote" #1. I would like for it to add the date of inventory list for each tote above the tote information on sheet 2. Example below.

Sheet 1 ( Tote Inventory )

7/15/17
Tote Number - Sku Number - UPC Number - Location
" " " " " " " "

and if I started inventory on tote # 2 the next day, this is how I want it to look on sheet 2 (Tote Inventory Running List ) when copied and pasted

(Tote 1 from day before)

7/15/17
Tote Number - Sku Number - UPC Number - Location
" " " " " " " "
7/16/17
Tote Number - Sku Number - UPC Number - Location
" " " " " " " "

 

[shawn2209]

Hello,

I am still a beginner to macros and learn quickly by reading forums on other's experiences with running macros. So let me explain what I am trying to do and I believe I am failing miserably. I am wanting to get the macro to copy the date from sheet 1 and paste it is cell A1 on sheet two and then take the information from selected cells on sheet 1 and past to sheet 2. At the same time I am wanting to erase the data from sheet 1 to where I can add more information on that sheet and then once I run the macro again, it copies the same data and paste it into sheet two at the next empty cell. I am wanting to keep a running list of inventory at my work with adding the inventory information for each "tote" and copy it into a running sheet (sheet 2). The issue is once I finish with the inventory in "tote #1", I am wanting the macro to erase the data to start the inventory list for "tote" #2 and the paste it onto sheet 2 on the next empty cell without deleting the information for "tote" #1. I would like for it to add the date of inventory list for each tote above the tote information on sheet 2. Example below.

Sheet 1 ( Tote Inventory )

7/15/17
Tote Number - Sku Number - UPC Number - Location
" " " " " " " "

and if I started inventory on tote # 2 the next day, this is how I want it to look on sheet 2 (Tote Inventory Running List ) when copied and pasted

(Tote 1 from day before)

7/15/17
Tote Number - Sku Number - UPC Number - Location
" " " " " " " "
7/16/17
Tote Number - Sku Number - UPC Number - Location
" " " " " " " "

Below is what I have so far.

Sub COPY()
'
' COPY Macro
'
'
Range("A1").Select
Sheets("Overage Tracking Report").Select
Range("A10").Select
Selection.COPY
Sheets("Overage Tracking Running Report").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("B2").Select
Sheets("Overage Tracking Report").Select
Range("A12:D44").Select
Selection.COPY
Sheets("Overage Tracking Running Report").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Application.CutCopyMode = False
End Sub


This copies the first tote in sheet 2 perfectly. I just need it to delete the information from "Overage Tracking Report" from cells A12:D44 and after I submit new information and have macro run again it grabs the information from tote #2 and past in the next empty cell on " Overage Tracking Running Report" and over and over again.

 

[MARK858]

Is tote #2 on Sheets("Overage Tracking Report") still in A10? and the range still Range("A12:D44") on Sheets("Overage Tracking Report") on the 2nd run?

and for all future totes?

 

[shawn2209]

Yes for tote 2 and all future totes will still use the same sheet and same range.

 

[MARK858]

Try the code below (please note it assumes the last row with data always has a value in column A, i.e. if row 44 had data in B44, C44, D44 but A44 was blank then you will get incorrect results and so we would need to change a few bits).

Sub CopyRng()
    Application.ScreenUpdating = False

    With Sheets("Overage Tracking Report")
        .Range("A10").COPY
        Sheets("Overage Tracking Running Report").Cells(Rows.Count, "B").End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlPasteValues
        .Range("A10").ClearContents

        .Range("A12:D44").COPY
        Sheets("Overage Tracking Running Report").Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        .Range("A12:D44").ClearContents
    End With

    With Sheets("Overage Tracking Running Report")
        If .Cells(1, "A") = "" Then .Rows(1).Delete
    End With

    Application.ScreenUpdating = True
End Sub