Merging two macros

Question

I have a sheet with raw data called "Raw Data" I want to copy this data into another sheet called "Data".

In "Raw Data" I have a Named Range called "RawTab1" In "Data" I have a Table called "DataTable" where I want to paste the data from range "RawTab1" but not the two first two rows from the range "RawTab1"

A have made this macro to cleare the data in the DataTable before pasting the new data from RawTab1:

    Dim sht As Worksheet
    Set sht = ThisWorkbook.Worksheets("Data")
        Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents
    End Sub

And this is the macro to copy the data from RawTab1 range into the DataTable

  Sub CopyRawTab1()
Application.Goto Reference:="RawTab1"
Selection.Copy
Sheets("Data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End Sub

So my questions are:

How can I merge these two macros?
How can avoide not to paste the two first rows from RawTab1 into the DataTable?

I recommend to read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Nov 22 '18 at 14:16

Pᴇʜ · Accepted Answer · 2018-11-22T14:27:55.987

1

It should be something like the following:

Option Explicit

Public Sub Combined()
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Worksheets("Data")
    sht.Range("A3:M3", sht.Range("A3:M3").End(xlDown)).ClearContents

    With Worksheets("Raw Data").Range("RawTab1")
        'copy everything from RawTab1 but not the first 2 rows
        .Resize(RowSize:=.Rows.Count - 2).Offset(RowOffset:=2).Copy
    End With

    sht.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Note that I used a combination of the Range.Resize property and the Range.Offset property to remove the first to rows of the RawTab1 range before copying it.

edited Nov 22 '18 at 14:27

answered Nov 22 '18 at 14:21

Pᴇʜ

56,719
10
49
73

1

@Peh, this is exactly what I was looking for, it works great. Thank you! – NewCalc Nov 22 '18 at 15:39
I have an additional challenge, with formatting text as number. The value in column J and K ( Year and Month) in "RawTab1" are formatted with an ' e.g. '2018. So my question is, is it possible to paste these two columns formatted as numbers? Otherwise the macro is working perfect. – NewCalc Jan 18 '19 at 15:35
@NewCalc We cannot follow up this here. Please open up a new question for that. And give a full example (or better a screenshot). Also see [mcve]. Comments are not sufficient to answer that. – Pᴇʜ Jan 18 '19 at 15:38
Done, created a [new quastion] (https://stackoverflow.com/questions/54257589/part-of-the-range-formatted-as-text) – NewCalc Jan 18 '19 at 16:09

Merging two macros

1 Answers1

Linked