Copy specific columns from excel worksheet to another worksheet

0
votes

I am new to excel coding, and I was wondering if someone could help me out with this little issue. I have an excel workbook with two worksheets Data_1 and Data_2, what I need is some type of VBA code that can copy data from Data_1 worksheet to Data_2 worksheet, however the only columns that I need from Data_1 are A,B,E,G,I,J,L,M without overwriting the previous data on data_2 since this will be updated on a daily basis. is this something that can be done?

2
vbaexcel
Yes it can be done. But, Stack Overflow is not a code for me service nor is it a teaching site. Stack Overflow is a place where one can bring code that is flawed and the community will help fix the problem. – Scott Craner
Thanks Scott I think I know its not a code for me service or teaching site, I figured that out alone can you believe that. Also thanks for answering the question at the same time, that's all i need it to know if it could be done. – Manic31

2 Answers

0
votes

Copy the Data can be done like this:

Sheets("Data_1").Range("A1:A100").Copy _ '100 is just an example
Sheets("Data_2").Range("A1:A100")

The Problem is, that this would overwrite the the previous entries, so you would have to change the Range every time.

To aviod doing that everytime try following these methods: Select Different Column Range Excel VBA and increase the values of the columns everytime (to paste it to other cells)

I hope this is helpful, if it's not, just comment :)

0
votes

Try this:

Sub Demo()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim path As String, fileName As String
    Dim lastRowInput As Long, lastRowOutput As Long, rowCntr As Long, lastColumn As Long
    Dim inputWS As Worksheet, outputWS As Worksheet

    'set your sheets here
    Set inputWS = ThisWorkbook.Sheets("Data_1")
    Set outputWS = ThisWorkbook.Sheets("Data_2")
    rowCntr = 1

    'get last rows from both sheets
    lastRowInput = inputWS.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowOutput = outputWS.Cells(Rows.Count, "A").End(xlUp).Row
    lastColumn = inputWS.Cells(1, Columns.Count).End(xlToLeft).Column

    'copy data from columns A, B, E, G, I, J, L and M
    inputWS.Range("A1:B" & lastRowInput).Copy outputWS.Range("A" & lastRowOutput + 1)
    inputWS.Range("E1:E" & lastRowInput).Copy outputWS.Range("E" & lastRowOutput + 1)
    inputWS.Range("G1:G" & lastRowInput).Copy outputWS.Range("G" & lastRowOutput + 1)
    inputWS.Range("I1:J" & lastRowInput).Copy outputWS.Range("I" & lastRowOutput + 1)
    inputWS.Range("L1:M" & lastRowInput).Copy outputWS.Range("L" & lastRowOutput + 1)


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Instead of copying ranges you can also assign values to ranges as:

Range(outputWS.Cells(lastRowOutput + 1, 1), outputWS.Cells(lastRowOutput + lastRowInput, 2)).Value = Range(inputWS.Cells(1, 1), inputWS.Cells(lastRowInput, 2)).Value
Range(outputWS.Cells(lastRowOutput + 1, 5), outputWS.Cells(lastRowOutput + lastRowInput, 5)).Value = Range(inputWS.Cells(1, 5), inputWS.Cells(lastRowInput, 5)).Value
Range(outputWS.Cells(lastRowOutput + 1, 7), outputWS.Cells(lastRowOutput + lastRowInput, 7)).Value = Range(inputWS.Cells(1, 7), inputWS.Cells(lastRowInput, 7)).Value
Range(outputWS.Cells(lastRowOutput + 1, 9), outputWS.Cells(lastRowOutput + lastRowInput, 10)).Value = Range(inputWS.Cells(1, 9), inputWS.Cells(lastRowInput, 10)).Value
Range(outputWS.Cells(lastRowOutput + 1, 12), outputWS.Cells(lastRowOutput + lastRowInput, 13)).Value = Range(inputWS.Cells(1, 12), inputWS.Cells(lastRowInput, 13)).Value