Help Please :)

[Auxie]

Hi.

Before I begin please note I'm a complete newbie this is my first attempt at writing code. - I'm running excel 2013

I'm trying to pull information from multiple spreadsheets into one "master" version. I spent the morning making the below code, which worked, I then expanded it to my other spreadsheets (20 in total), I went to run the macro and it will complete the first line then spit out an application/object defined error. Could anyone give me some tips of what I've done wrong and/or fix it

Workbooks.Open Filename:= _
        "X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\IPW-CAP-00-XX-RE-Z-0001.xlsx"
            
    Windows("Document Tracker.xlsm").Activate
    
    'TQ's
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "='[IPW-CAP-00-XX-RE-Z-0001.xlsx]Framework'!R6C4"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "='[IPW-CAP-00-XX-RE-Z-0001.xlsx]Framwork'!R7C4"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "='[IPW-CAP-00-XX-RE-Z-0001.xlsx]Framework'!R8C4"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "='[IPW-CAP-00-XX-RE-Z-0001.xlsx]Framework'!R9C4"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "='[IPW-CAP-00-XX-RE-Z-0001.xlsx]Framework'!R10C4"
    
    'Paste as values
    Range("B7:B11").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=zlNone, SkipBlanks _
    :=False, Transpose:=False

The code continues but, it's pretty much the same type of code... Do I have to define the activecells? if so, tips on how to do that please.. getting rather confusing. Also I'd admit that the "activecell" lines were generated using the Record macro, thinking that I'd could just use that.

Cheers in advance.

[Jan Karel Pieterse]

Looks like that could be replaced with:

    Dim oCurSht As Worksheet
    Set oCurSht = ActiveSheet
    Workbooks.Open Filename:= _
                   "X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\IPW-CAP-00-XX-RE-Z-0001.xlsx"
    oCurSht.Range("B7:B11").Value = ActiveSheet.Range("D6:D10").Value

[SamT]

Range("B7").Select
ActiveCell.FormulaR1C1 =

That structure should always be replaced with
Range("B7").FormulaR1C1 =

The same is true of
Something.Select
Selection.BlahBlah

Should be edited to read
Something.BlahBlah

[Paul_Hossler]

You could modularize this a lot more by having a sub to process a file that takes the path, source range, and destination range in Master

This is very crude

Option Explicit

Sub Alt1_Demo()
    Dim sFilename As String
    Dim wb1 As Workbook, wb2 As Workbook
    
    Set wb1 = ThisWorkbook  '   probably "Document Tracker.xlsm"
    
    sFilename = "IPW-CAP-00-XX-RE-Z-0001.xlsx"
     
     Set wb2 = Workbooks.Open (Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename)
     
    wb2.Worksheets("Framework").Range("D6:D10").Copy
    wb1.Worksheets("Something").Range("B7:B11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    wb2.Close False
End Sub

[Auxie]

You could modularize this a lot more by having a sub to process a file that takes the path, source range, and destination range in Master

This is very crude

Option Explicit

Sub Alt1_Demo()
    Dim sFilename As String
    Dim wb1 As Workbook, wb2 As Workbook
    
    Set wb1 = ThisWorkbook  '   probably "Document Tracker.xlsm"
    
    sFilename = "IPW-CAP-00-XX-RE-Z-0001.xlsx"
     
    Workbooks.Open Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename
     
    wb2.Worksheets("Framework").Range("D6:D10").Copy
    wb1.Worksheets("Something").Range("B7:B11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    wb2.Close False
End Sub

Complete newb questions so apologises in advance! -

With set wb1; do i have to link this to the workbook i want the data sent to? and additionally do i have to reference a wb2 to the workbook i am copying from?

for example;
wb 1 it would be: Set wb1 = "document tracker.xls"
wb 2 would be: Set wb2 = "documentcopyfrom.xls"

[Bob Phillips]

Yes that is so. Your code also looks good, assuming both files are open.

[mdmackillop]

Paul's code above corrected to set variable

Set wb2 = Workbooks.Open (Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename)

[Auxie]

Don't think I'm understanding, the below kicks back an object error

Sub Alt1_Demo()    

Dim sFilename As String    
Dim wb1 As Workbook, wb2 As Workbook 
        
Set wb1 = ThisWorkbook ("Document Tracker.xlsx")   
Set wb2 = ThisWorkbook ("IPW-CAP-00-XX-RE-Z-0001.xlsx") 
sFilename = "IPW-CAP-00-XX-RE-Z-0001.xlsx" 

Workbooks.Open Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename         wb2.Worksheets("Framework").Range("D6:D10").Copy    
wb1.Worksheets("Sheet 1").Range("B7:B11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb2.Close False

End Sub

[Auxie]

Paul's code above corrected to set variable

Set wb2 = Workbooks.Open (Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename)

Excellent thank you!

[mdmackillop]

Also, it is sufficient to paste to a single cell; less prone to error

wb1.Worksheets("Something").Range("B7").PasteSpecial .....

[Jan Karel Pieterse]

ThisWorkbook is a predefined object and is pointing to the workbook that contains the macro itself. Since ThisWorkbook always exists there is no need for a variable to hold ThisWorkbook (in this case wb1 was used). Thisworkbook also has no arguments so does not need any brackets, hence the object error. Your code corrected:

Sub Alt1_Demo()    
    Dim sFilename As String 
    Dim wb2 As Workbook 
        
    sFilename = "IPW-CAP-00-XX-RE-Z-0001.xlsx" 

    Set wb2=Workbooks.Open(Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename)
    wb2.Worksheets("Framework").Range("D6:D10").Copy    
    ThisWorkbook.Worksheets("Sheet 1").Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    wb2.Close False

End Sub

You can make this generic like so:

Sub DoSomeFiles()
    ProcessFile "IPW-CAP-00-XX-RE-Z-0001.xlsx" 
    ProcessFile "IPW-CAP-00-XX-RE-Z-0002.xlsx"
    ProcessFile "IPW-CAP-00-XX-RE-Z-0003.xlsx"
End Sub

Sub ProcessFile(sFilename As String)
 
Dim wb2 As Workbook 
        
Set wb2=Workbooks.Open(Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename)
wb2.Worksheets("Framework").Range("D6:D10").Copy    
ThisWorkbook.Worksheets("Sheet 1").Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

wb2.Close False

End Sub

[Auxie]

Thanks guys. Been able to cobble something together.

I seem to need to get a better grasp of the basics, I've been using Youtube videos to get guidance for what i want but they don't seem to skip the bare-bones of things. Do any of you have any good resources that'll help me learn this?

Thanks again!

[Paul_Hossler]

Paul's code above corrected to set variable

Set wb2 = Workbooks.Open (Filename:="X:\089872 - IPW - Framework\02 Admin\01 DocRegisters\" & sFilename)

@Mac -- Thanks - I was simplifying and goofed



@JKP - re. wb1 -- yes, you're 100% correct, but I figured it'd be clearer to emphasize the different WBs that way. wb1 is not really required, but my (very) personal preference when I have 2 or more WBs open is to be extremely specific, and esp NEVER rely on ActiveWorkbook or ActiveSheet

[Bob Phillips]

@JKP - re. wb1 -- yes, you're 100% correct, but I figured it'd be clearer to emphasize the different WBs that way. wb1 is not really required, but my (very) personal preference when I have 2 or more WBs open is to be extremely specific, and esp NEVER rely on ActiveWorkbook or ActiveSheet

I agree, that is a good practice IMO.