Excel VBA Code for Copy & Paste Without Header, Paste in Last Row

[jxj_00]

Hi, please help as I am very new to VBA.

I need to copy and paste from Workbook 1, Worksheet 1 to Workbook 2, Worksheet 2.
There are 2 main things I need to achieve with the code.
1. Copy from WB1, WS1 from row 4 to WB2, WS2 row 4 (As I have headers & macro buttons above)
2. Paste to WB2, WS2 at the last empty row

The code runs successfully.
However, it tells me to select a destination and press enter. However, the code I have written is supposed to add it to last empty row.

Please help and thank you in advance!

Sub Copy_Without_Header()

'Delete Empty Rows'
On Error Resume Next
Range("B3:B" & Workbooks("WB1.xlsm").Worksheets("WS1"). _
UsedRange.Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Copy without header'
Workbooks("WB1.xlsm").Worksheets("WS1"). _
Range("A4").CurrentRegion.Offset(1, 0).Resize(Range("A4").CurrentRegion.Rows.Count - 1).Copy

'Paste in last empty row'
Workbooks("WB2.xlsm").Worksheets("WS2"). _
Range("A4").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats

End Sub

 

[9dal39]

You are very welcome.

Hi mumps, similar to the above I sort of hijacked the code you gave user above, but I need it to do something slightly different, so I need it to copy all the data from in this example WS1(WS1 DATA) in WB1(EXAMPLE BOOK), to WS2(WS2 END DEST) in that WB(EXAMPLE BOOK), my problem is I need it to check for the last row that has data present in column B (as column A populates all the way down usually), and once it finds the last one with data in row B on WS2, it then pastes in the next row from COLUMN A to P of all the data copied from WS1.

on my sheets ROW 1 is the headers, so from row 2 is what I copy in the source sheet. so in short, I want it to check column B on WS2 for the last entry, then in the row below that copy and paste all the data from WS1 into these rows, overwriting anything in the cells below as aslong as its below the last row with data in COLUMN B this would be perfect.

or by copying to the last row by a filter on the table?

I don't know if you can go through the VBA code above with ' explaining each point and whats its targeting / doing so I can work or if you can help mind?

is it possible for WS1 to pull data based on the status in column L? whether a column has "Closed" or 3 or 4 of the filters set?

I have uploaded the EXAMPLE book file for you to take a look at here: Gofile

So I want it the check the column I've highlighted in RED on WS2 in the example I sent the next free row is highlighted in yellow ROW 20, then copy and paste all data highlighted in yellow from A2:P15 in the example book into A20 overwriting anything it needs too.

Also if theres another method I can use that would copy all of the data from WS1 by filtering the status to for example "Closed" and only copy that data that would be great, p.s. I don't need it to delete any blanks or anything this isn't a problem. please let me know if you can help!

 

[mumps]

This macro will copy the data after filtering the status on "Closed".

Sub Copy_Without_Header_EDIT_MYVBASOLUTION()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, LastRow As Long, lRow As Long
    ' NAME OF SHEET TO COPY FROM IS THE SOURCE
    Set srcWS = Sheets("WS1 DATA")
    ' NAME OF SHEET TO COPY TO IS THE DESTINATION
    Set desWS = Sheets("WS2 END DEST")
    LastRow = desWS.Range("B" & desWS.Rows.Count).End(xlUp).Row + 1
    'lRow = srcWS.Range("B" & desWS.Rows.Count).End(xlUp).Row
    With srcWS
        .Cells(1, 1).AutoFilter 12, "Closed"
        .AutoFilter.Range.Offset(1).Copy desWS.Range("A" & LastRow)
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[9dal39]

This macro will copy the data after filtering the status on "Closed".

Sub Copy_Without_Header_EDIT_MYVBASOLUTION()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, LastRow As Long, lRow As Long
    ' NAME OF SHEET TO COPY FROM IS THE SOURCE
    Set srcWS = Sheets("WS1 DATA")
    ' NAME OF SHEET TO COPY TO IS THE DESTINATION
    Set desWS = Sheets("WS2 END DEST")
    LastRow = desWS.Range("B" & desWS.Rows.Count).End(xlUp).Row + 1
    'lRow = srcWS.Range("B" & desWS.Rows.Count).End(xlUp).Row
    With srcWS
        .Cells(1, 1).AutoFilter 12, "Closed"
        .AutoFilter.Range.Offset(1).Copy desWS.Range("A" & LastRow)
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Hi mate one more question how do I make that part that is filter by Closed to filter by Closed and something else for example to filter by both "Pending Assignment" & "Work In Progress" ?

 

[mumps]

That would depend on how many criteria you want to use. Will the number of criteria always be the same and if so, how many criteria? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, when replying, please click the "Reply" button instead of the "Reply With Quote" button to avoid clutter.

 

[9dal39]

That would depend on how many criteria you want to use. Will the number of criteria always be the same and if so, how many criteria? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). Also, when replying, please click the "Reply" button instead of the "Reply With Quote" button to avoid clutter.

Hi mate I have added an example workbook again as I thought its easier again, basically it'll be so the filtering needs to by the data on each sheet I thought if i seperate them out into seperate sheets it makes life easier and obviously I cutdown the data massively but sometimes these copies are in the 1000's so I basically want the same thing as before to happen, but to move the data from SDMStationIN, filtering by all of the different unique values in the Status column, I believe these to be
Assigned
New
On Hold - Clock Stopped
On Hold - Clock Running
Work In Progress
If you could explain how I would say add a different value to this in future in the code, and obviously remove them and what values I need to change that would be brilliant?

so for instance if I wanted to only filter by Assigned and New and not do it by the other 3 what would I change from doing it with all 5 of the above? thank you so much for your help!

 

[9dal39]

Gofile - Free Unlimited File Sharing and Storage

Gofile is a free, secure file sharing and storage platform. With unlimited bandwidth and storage, you can easily store and share files of any type without any limits. Our advanced features, such as CDN support and password protection, make Gofile the ideal choice for individuals and businesses...

gofile.io

here is the spreadsheet apologies forgot to attach!

 

[mumps]

I still don't quite understand what you want to do. Looking at the 2 macros in your workbook, it looks like you want to filter the "SDMstationIN CLOSED" sheet on "Closed" in the Status column and paste the filtered data to sheet "All CLOSED". You also want to filter the ""SDMstationIN RESOLVED"" sheet on "Resolved" in the Status column and paste the filtered data to sheet "All RESOLVED". Is this what you want to do? Please explain in detail which sheets you want to filter, the filter criteria for each sheet and where you want to paste the filtered data from each sheet.

 

[9dal39]

Apologies no its just SDMstationIN to ALL OPEN, so all of the data in all open filtering by the different statuses, but then if I go in and edit it what do I have to remove to only filter by say New and Work in progress, instead of filtering all 5 codes of the data, reason im filtering all the unique ids is when the macro i use in my real sheet copies the data to these pages it sometimes has additionals, so filtering by the 5 statuses i mentioned on the previous post it would make sure only those are copied if that makes sense? So all resolved is fine, and all closed is working fine atm, its just the SDMstationIN and ALL OPEN, i need to work does that make sense?
So what would I have to use to filter by all 5 statuses then copy over the same as the other sheets do currently? And then what would I change in that code to filter by 2 of the 5 instead of all 5?

 

[mumps]

Try:

Sub Copy_FromSDMstationIN_TO_ALLOPEN()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, LastRow As Long, arr As Variant
    arr = Array("Assigned", "New", "On Hold - Clock Stopped", "On Hold - Clock Running", "Work In Progress")
    ' NAME OF SHEET TO COPY FROM IS THE SOURCE
    Set srcWS = Sheets("SDMstationIN")
    ' NAME OF SHEET TO COPY TO IS THE DESTINATION
    Set desWS = Sheets("All OPEN")
    LastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row + 1
    With srcWS
        .Cells(1, 1).AutoFilter 10, Criteria1:=arr, Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).Copy desWS.Range("A" & LastRow)
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

You can add or delete a criterium simply by changing the values in the array (in red).

 

[9dal39]

Try:

Sub Copy_FromSDMstationIN_TO_ALLOPEN()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, LastRow As Long, arr As Variant
    arr = Array("Assigned", "New", "On Hold - Clock Stopped", "On Hold - Clock Running", "Work In Progress")
    ' NAME OF SHEET TO COPY FROM IS THE SOURCE
    Set srcWS = Sheets("SDMstationIN")
    ' NAME OF SHEET TO COPY TO IS THE DESTINATION
    Set desWS = Sheets("All OPEN")
    LastRow = desWS.Range("A" & desWS.Rows.Count).End(xlUp).Row + 1
    With srcWS
        .Cells(1, 1).AutoFilter 10, Criteria1:=arr, Operator:=xlFilterValues
        .AutoFilter.Range.Offset(1).Copy desWS.Range("A" & LastRow)
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

You can add or delete a criterium simply by changing the values in the array (in red).

Thanks so much your a life saver!