Snasui Excel Forum

Code: Select all

 Sub Update()
'
' Update Macro
'

'
    Windows("Data.xlsx").Activate
    Sheets("Sheet2").Select
    Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H6").Select
    Windows("SumData.xlsm").Activate
    Range("B3").Select
    ActiveSheet.Paste
    Range("H5").Select
    Windows("Data.xlsx").Activate
    Range("H9").Select
    Windows("SumData.xlsm").Activate
    Range("I4").Select
    Windows("Data.xlsx").Activate
    Sheets("Sheet1").Select
    Range("B3:F3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("SumData.xlsm").Activate
    Range("H3").Select
End Sub
  

Code: Select all

Sub test0()
    Dim sb As Workbook, rs As Range
    Dim tb As Workbook
    Dim sh As Worksheet
    
    Set sb = Workbooks("Data.xlsx")
    Set tb = ThisWorkbook
    For Each sh In sb.Worksheets
        Set rs = sh.Range("b3", sh.Range("b" & sh.Rows.Count).End(xlUp))
        With tb.Worksheets("SumAllData")
            With .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Resize(rs.Rows.Count, 5).Value = rs.Resize(, 5).Value
            End With
        End With
    Next sh
End Sub

Code: Select all

 Sub run()
'
' run Macro
'

'
    Windows("Data2.xlsx").Activate
    Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Windows("SumData2.xlsm").Activate
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J6").Select
End Sub

Code: Select all

Sub test2()
    Dim sb As Workbook, rs As Range
    Dim tb As Workbook
    Dim sh As Worksheet
    
    Set sb = Workbooks("Data2.xlsx")
    Set tb = ThisWorkbook
    For Each sh In sb.Worksheets
        Set rs = sh.Range("b3", sh.Range("b" & sh.Rows.Count).End(xlUp))
        With tb.Worksheets("Sum_" & Format(Range("D1"), "mmyy"))
            With .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Resize(rs.Rows.Count, 5).Value = rs.Resize(, 5).Value
            End With
        End With
    Next sh
End Sub

Code: Select all

Sub Updates()
    Dim sb As Workbook, rs As Range
    Dim tb As Workbook
    Dim sh As Worksheet
    
    Set sb = Workbooks("Data2.xlsx")
    Set tb = ThisWorkbook
    For Each sh In sb.Worksheets
        Set rs = sh.Range("b3", sh.Range("b" & sh.Rows.Count).End(xlUp))
        With tb.Worksheets("Sum_" & Format(Range("D1"), "mmyy"))
            With .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Resize(rs.Rows.Count, 5).Value = rs.Resize(, 5).Value
            End With
        End With
    Next sh
End Sub

Code: Select all

  With tb.Worksheets("Sum_" & Format(Range("D1"), "mmyy")) 

Code: Select all

'Other code
tb.Activate
With tb.Worksheets("Sum_" & Format(Range("D1"), "mmyy"))
    With .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
        .Resize(rs.Rows.Count, 5).Value = rs.Resize(, 5).Value
    End With
End With
'Other code

Code: Select all

Sub Updates()
    Dim sb As Workbook, rs As Range
    Dim tb As Workbook
    Dim sh As Worksheet
    
    Set sb = Workbooks("Data2.xlsx")
    Set tb = Workbooks("SumData_bymonth.xlsx")
    For Each sh In sb.Worksheets
        Set rs = sh.Range("b3", sh.Range("b" & sh.Rows.Count).End(xlUp))
    tb.Activate
        With tb.Worksheets("Sum_" & Format(Range("D1"), "mmyy"))
            With .Range("b" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Resize(rs.Rows.Count, 5).Value = rs.Resize(, 5).Value
            End With
        End With
    Next sh
End Sub