Reactivating last active workbook: need help!

[Ams47]

Hello,

This is my first time posting. Everyday my operators collect data and save them as a WB with the date as the name. I have created a "Master" WB where I copy and store only specific cells from the operator's WBs. Due to the nature of the formatting in the WBs I need to constantly go back and forth copying and pasting the items I need. I've come up with a code to copy and paste the specific cells I need, but I have to manually activate the operators workbook every time I need to copy any items after the first items have been copied and pasted. Here is what I have so far.

Sub CopyToMaster()


Windows("Today's Date.xlsm").Activate <--- [I've changed this line to "ActiveWindow.Activate" to allow me to reference the WB I have open currently]
Range("P5:P6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select <--- [I am using this to paste items into the next blank row]
Selection.Paste
Windows("Today's Date.xlsm").Activate
Range("P7:P8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Windows("Today's Date.xlsm").Activate
Range("P9:P10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("E" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Windows("Today's Date.xlsm").Activate
Range("P11:P12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("G" & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
End Sub

Is there a way for excel to "remember" the active workbook I copied from the first time (The one in the first line of the code), activate it and copy/paste the items I need automatically? I can't keep it referenced as "Today's Date" as that is the variable that keeps changing. I hope this is detailed enough. I am sorry if there is a problem with the way I am posting this, I am very new to all this, just started today! Any help will be much appreciated! Thanks in advance!

 

[DanteAmor]

Try the following. Change de format date in the macro, or tell me an example of the name of your book

Sub CopyToMaster()
    Set l1 = ThisWorkbook                                   'book with the running macro "Master List.xlsm"
    Set h1 = l1.ActiveSheet
    '
    Set l2 = Workbooks(Format(Date, "[COLOR=#0000ff]yyyymmdd[/COLOR]") & ".xlsm")  'Today's Date.xlsm. for example 20190103
    Set h2 = l2.ActiveSheet
    '
    h2.Range("P5:P6").Copy h1.Range("A" & h1.Range("A" & Rows.Count).End(xlUp).Row + 1)
    h2.Range("P7:P8").Copy h1.Range("C" & h1.Range("C" & Rows.Count).End(xlUp).Row + 1)
    h2.Range("P9:P10").Copy h1.Range("E" & h1.Range("E" & Rows.Count).End(xlUp).Row + 1)
    h2.Range("P11:P12").Copy h1.Range("G" & h1.Range("G" & Rows.Count).End(xlUp).Row + 1)
End Sub

Regards Dante Amor

 

[Ams47]

Thank you for responding!
The name of the Workbook I am trying to copy from is in the format "mm-dd-yyyy". Ex: 01-03-2019, 01-04-2019 (for the next day) and so on. I've been able to solve this problem by referencing the workbook as an index, but this will always require me to open my workbooks in a certain order. I have attempted the code you provided but for some reason it only copies the cells format and empties out the cells it is trying to copy.

Thanks,
AMS47

 

[DanteAmor]

Do you have formulas in the sheets of the book today? Then try the following

Sub CopyToMaster()
    Set l1 = ThisWorkbook                                   'book with the running macro "Master List.xlsm"
    Set h1 = l1.ActiveSheet
    '
    Set l2 = Workbooks(Format(Date, "mm-dd-yyyy") & ".xlsm")  'Today's Date.xlsm. for example 01-03-2019
    Set h2 = l2.ActiveSheet
    '
    h2.Range("P5:P6").Copy
    h1.Range("A" & h1.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues
    h2.Range("P7:P8").Copy
    h1.Range("C" & h1.Range("C" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=XValues
    h2.Range("P9:P10").Copy
    h1.Range("E" & h1.Range("E" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=XValues
    h2.Range("P11:P12").Copy
    h1.Range("G" & h1.Range("G" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=XValues
End Sub

Test it and tell me.

 

[Ams47]

So the code you have is successful in pasting the first copied values, but once it hits the second line it gives an error. "PasteSpecial method of Range class failed". However I have played around with my old code using your way of referencing the workbook name as its format.
Instead of referencing the WB as an index, I used your method of referencing and it seems to be working perfectly now.

Sub CopytoMaster()
Workbooks(Format(Date, "mm-dd-yyyy") & ".xlsm").Activate
Range("P5:P6").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Workbooks(Format(Date, "mm-dd-yyyy") & ".xlsm").Activate
Range("P7:P8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("C" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Workbooks(Format(Date, "mm-dd-yyyy") & ".xlsm").Activate
Range("P9:P10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("E" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Workbooks(Format(Date, "mm-dd-yyyy") & ".xlsm").Activate
Range("P11:P12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Master List.xlsm").Activate
Range("G" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Thank you for all the help!! I didn't know that I could reference based off of format! This will do wonders to my various projects!

Best Regards to you my friend,

Ams47

 

[DanteAmor]

Missing a letter l

Sub CopyToMaster()
    Set l1 = ThisWorkbook                                   'book with the running macro "Master List.xlsm"
    Set h1 = l1.ActiveSheet
    '
    Set l2 = Workbooks(Format(Date, "mm-dd-yyyy") & ".xlsm")  'Today's Date.xlsm. for example 01-03-2019
    Set h2 = l2.ActiveSheet
    '
    h2.Range("P5:P6").Copy
    h1.Range("A" & h1.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=xlValues
    h2.Range("P7:P8").Copy
    h1.Range("C" & h1.Range("C" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=X[COLOR=#ff0000]l[/COLOR]Values
    h2.Range("P9:P10").Copy
    h1.Range("E" & h1.Range("E" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=X[COLOR=#ff0000]l[/COLOR]Values
    h2.Range("P11:P12").Copy
    h1.Range("G" & h1.Range("G" & Rows.Count).End(xlUp).Row + 1).PasteSpecial Paste:=X[COLOR=#ff0000]l[/COLOR]Values
End Sub

In any case, it's good that it works.