PDA

View Full Version : [SOLVED] Help Please :)



Auxie
05-24-2017, 08:34 AM
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
05-24-2017, 09:33 AM
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
05-24-2017, 12:12 PM
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
05-24-2017, 07:04 PM
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
05-25-2017, 01:03 AM
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
05-25-2017, 01:30 AM
Yes that is so. Your code also looks good, assuming both files are open.

mdmackillop
05-25-2017, 01:46 AM
Paul's code above corrected to set variable

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

Auxie
05-25-2017, 01:55 AM
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
05-25-2017, 02:03 AM
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
05-25-2017, 02:44 AM
Also, it is sufficient to paste to a single cell; less prone to error

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

Jan Karel Pieterse
05-25-2017, 03:12 AM
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
05-25-2017, 03:28 AM
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
05-25-2017, 05:44 AM
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
05-25-2017, 05:58 AM
@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.