Macro to copy certain columns and paste in new sheet

[Zyphon]

Hi

I wonder if someone can help me, I need some code to help me copy the ranges in certain columns and paste them into a new worksheet. I have raw data on my "Data" worksheet but I need to export this into a word doc as the fields on the source data sheet are all over the place.

This is what I have so far to create the new sheet:

Sub CopyData()

    Dim WS As Worksheet
    Set WS = Sheets.Add
    WS.Name = "Export_Data"
    
    Sheets("Data_Source").Select
    Range("M1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Data_Source").Select
    Range("I1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
     Sheets("Data_Source").Select
    Range("J1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Data_Source").Select
    Range("K1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Now that the "Export Data" sheet has been created I wanted a macro that would copy the values from the following ranges/sheets:

            Data source       Export Data
Column            M      to         A
                  I      to         B
                  J      to         C
                  K      to         D

This isn't probably the most efficient use of code but my VBA skills are limited. The trouble I have is some of the cells are blank so I would need to select from row 1 then filter up from row 65536 that that the whole range in the column would be selected.

This would also make the importing of the necessary data much easier for me as some of it has to be inputted manually.

Any help would be gratefully received.

Thanks.

[Zyphon]

I have managed to pull off what I needed, it is not the slickest of code but my VBA skills are minimal. Maybe the copy and paste values could be stored in registers but again my skills are limited in VBA.

I thought I would post the code I have for anyone else who may find it useful.

Sub CopyData()

    Dim WS As Worksheet
    Set WS = Sheets.Add
    WS.Name = "Export_Data"
    
    Sheets("Data_Source").Select
    Range("M1").Select
    Range("M1", (Cells(65536, ActiveCell.Column).End(xlUp))).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Sheets("Data_Source").Select
    Range("I1").Select
    Range("I1", (Cells(65536, ActiveCell.Column).End(xlUp))).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Data_Source").Select
    Range("J1").Select
    Range("J1", (Cells(65536, ActiveCell.Column).End(xlUp))).Select
    Selection.Copy
    
    Sheets("Export_Data").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Data_Source").Select
    Range("K1").Select
    Range("K1", (Cells(65536, ActiveCell.Column).End(xlUp))).Select
    Selection.Copy
      
    Sheets("Export_Data").Select
    Range("D1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub