Need help: copy from one workbook to another and sorting them

[icyskullz]

I tried using record macro to copy data from "sample start" to "masterbook". But it ended up with an error.

What I did:
I click record
Select cell A2:I300, then copy
Open Masterbook
Paste it at the far bottom (around the last few row of the sheet)
Highlight column A to I
Click filter
Sort fund from A to Z
Sort date from A to Z
Save then close Masterbook
Stop macro
Set the macro to "transfer" bottom on the sheet (not the one on the form)

It came out with an error "subscript out of range"
I have no idea what that means.

 

[Luke M]

Hi icyskullz,

Change the transfer macro to this. I made a few assumptions about which sheet/ranges you're dealing with, so feel free to adjust if needed.

Sub Transfer()
    Dim wbMaster As Workbook
    Dim rngCopy As Range
    Dim wsDest As Worksheet
   
    Application.ScreenUpdating = False
   
    'What range to copy?
    Set rngCopy = Range("A2:I300")

    'Where is the master workbook stored?
    'I'll assume in same folder as this workbook
    Set wbMaster = Workbooks.Open(ThisWorkbook.Path & "\Masterbook.xlsx")
   
    'Which sheet in master workbook do we deal with?
    Set wsDest = wbMaster.Worksheets(1)
    With wsDest
        'Copy values over
        rngCopy.Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
       
        'Apply filter
        .Columns("A:I").AutoFilter
       
        'Sort sheet
        With .AutoFilter.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Intersect(wsDest.UsedRange.Offset(1), wsDest.Range("B:B")), Order:=xlAscending
            .SortFields.Add Key:=Intersect(wsDest.UsedRange.Offset(1), wsDest.Range("D:D")), Order:=xlAscending
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    End With
    wbMaster.Close savechanges:=True
   
    Application.ScreenUpdating = True
End Sub

 

[icyskullz]

Thanks! It works just fine!

 

[icyskullz]

Hi icyskullz,

Change the transfer macro to this. I made a few assumptions about which sheet/ranges you're dealing with, so feel free to adjust if needed.

Sub Transfer()
    Dim wbMaster As Workbook
    Dim rngCopy As Range
    Dim wsDest As Worksheet
  
    Application.ScreenUpdating = False
  
    'What range to copy?
    Set rngCopy = Range("A2:I300")

    'Where is the master workbook stored?
    'I'll assume in same folder as this workbook
    Set wbMaster = Workbooks.Open(ThisWorkbook.Path & "\Masterbook.xlsx")
  
    'Which sheet in master workbook do we deal with?
    Set wsDest = wbMaster.Worksheets(1)
    With wsDest
        'Copy values over
        rngCopy.Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
      
        'Apply filter
        .Columns("A:I").AutoFilter
      
        'Sort sheet
        With .AutoFilter.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Intersect(wsDest.UsedRange.Offset(1), wsDest.Range("B:B")), Order:=xlAscending
            .SortFields.Add Key:=Intersect(wsDest.UsedRange.Offset(1), wsDest.Range("D:D")), Order:=xlAscending
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    End With
    wbMaster.Close savechanges:=True
  
    Application.ScreenUpdating = True
End Sub

There is something wrong when I tried again. It says object variable or with block variable not set. Highlighted: With .AutoFilter.Sort
Please help

 

[Luke M]

Ah, I see the problem. I forgot to check first if AutoFilter was already on. Change code to this

Sub Transfer()
    Dim wbMaster As Workbook
    Dim rngCopy As Range
    Dim wsDest As Worksheet
   
    Application.ScreenUpdating = False
   
    'What range to copy?
    Set rngCopy = Range("A2:I300")

    'Where is the master workbook stored?
    'I'll assume in same folder as this workbook
    Set wbMaster = Workbooks.Open(ThisWorkbook.Path & "\Masterbook.xlsx")
   
    'Which sheet in master workbook do we deal with?
    Set wsDest = wbMaster.Worksheets(1)
    With wsDest
        'Apply filter if needed
        If .AutoFilterMode = False Then
            .Columns("A:I").AutoFilter
        Else
            .ShowAllData
        End If

        'Copy values over
        rngCopy.Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
              
        'Sort sheet
        With .AutoFilter.Sort
            .SortFields.Clear
            .SortFields.Add Key:=Intersect(wsDest.UsedRange.Offset(1), wsDest.Range("B:B")), Order:=xlAscending
            .SortFields.Add Key:=Intersect(wsDest.UsedRange.Offset(1), wsDest.Range("D:D")), Order:=xlAscending
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    End With
    wbMaster.Close savechanges:=True
   
    Application.ScreenUpdating = True
End Sub