Copy data from one sheet and paste into the first

Question

Sub ImportFixed()
'
Sheets("Front-Page").Select
    Sheets("SPROC").Visible = True
Sheets("SPROC").Select
ThisWorkbook.RefreshALL
DoEvents
    'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("SPROC").Select
    Range("J2").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Master-Data-Sheet").Select
    Range("A1914").Select
    ActiveSheet.Paste
    Sheets("SPROC").Select

    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master-Data-Sheet").Columns("N:N").Range("N1914").Paste

    Cells.Select
    Application.CutCopyMode = False
    With Selection.Font
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
    End With
    With Selection.Font
        .Name = "Calibri"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Columns("A:H").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Columns("M:N").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("L16108").Select
    Range("J2105").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("J2137").Select
    Range("N2137").Select
    Sheets("SPROC").Select
    ActiveWindow.SelectedSheets.Visible = False
    ActiveWindow.ScrollWorkbookTabs Sheets:=-2
    Sheets("Master-Data-Sheet").Select
End Sub

I have a report that has a sheet named SPROC. This sheet is refreshed each Monday and pulls through data for that day from a SQL query (any other data on that sheet is overwritten) . What I then want to do is select ALL the data (Columns A:N - The number of rows changes each week so the range isn't fixed) and paste it into the first blank cell in column A on a sheet named Master-Data-Sheet. This second sheet contains ALL the data for previous weeks and is used to populate ALL my pivot tables and graphs etc on various other worksheets. At present I have recorded a Macro but instead of finding the last blank row, it is using a specific range which means that when I run the macro, it overwrites data in the Master Data file. Any Suggestions?

I have included a copy of the VBA code (it also does a lot of other functions so apologies if it is a little long). I think it is lines 20 and 359 where the issue is occurring but I have no idea what to do to fix it (I have tried ALL manner of different variations).

Answer 1

Pretty classical matter, must have a lot of similar question and please get rid of scrolls and things like this in record macros...

try this :

Sub Macro2()
'
Dim ShIn As Worksheet
Dim ShOut As Worksheet
Set ShIn = ThisWorkbook.Sheets("SPROC")
Set ShOut = ThisWorkbook.Sheets("Master-Data-Sheet")

'ShIn.Cells(2, 1).End(xlToRight).Column
Dim RgTotalInput As String
Dim RgTotalOutput As String

RgTotalInput = "$A$2:$" & ColLet(ShIn.Cells(1, 1).End(xlToRight).Column) & "$" & ShIn.Cells(Rows.Count, 1).End(xlUp).Row
RgTotalOutput = "$A$" & ShOut.Cells(Rows.Count, 1).End(xlUp).Row + 1

ShIn.Range(RgTotalInput).Copy Destination:=ShOut.Range(RgTotalOutput)


End Sub



Public Function ColLet(ByVal ColNb As Integer) As String
Dim ColLetTemp As String

Select Case ColNb
    Case Is < 27
        ColLetTemp = Chr(64 + ColNb)
    Case Is > 26
        If Int(ColNb / 26) <> ColNb / 26 Then
            ColLetTemp = Chr(64 + Int(ColNb / 26)) & Chr(64 + ColNb - 26 * Int(ColNb / 26))
        Else
            ColLetTemp = Chr(64 + Int(ColNb / 26) - 1) & Chr(64 + 26)
        End If
    Case Else

End Select

ColLet = ColLetTemp
End Function