Setting Variables

[Karen McKenzie]

I've compiled the following code to open a file (determined by value in range
D10), identify range of cells to be copied, then copy them into the next
available row in another spreadsheet.

Sub Import()


Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet
Dim FileName As Workbook

Set DestinationWB = ThisWorkbook 'This file
Set DestinationWS = DestinationWB.Worksheets("Import") 'This file Sheet
"Import"
Set FileName = ThisWorkbook.Worksheets("Data").Range("d10") 'contents of
this file, sheet "data", Cell D10
Set SourceWB = FileName

Workbooks.Open SourceWB, UpdateLinks:=0 'Open the file named in cell D10
on on sheet "data"
ActiveSheet.Select
Let HiddenCells = Range("AV3")
ActiveSheet.Unprotect Password:=HiddenCells 'Unprotect Sheet
Range("AG72:B72").Select
Range(Selection, Selection.End(xlDown)).Select 'Select rows from row 72
down which has data in column AG
Selection.Copy Destination:=DestinationWB.DestinationWS.Range("A2").Select
'Paste data selected above into this workbook
'sheet "import" after last row with data in column A
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
SourceWB.Protect Password:=HiddenCells, DrawingObjects:=True,
Contents:=True, Scenarios:=True 'Reprotect Workbook
SourceWB.Save
SourceWB.Close
End Sub

I'm getting nowhere with this and am hoping someone can help me with the
code. I'm trying to learn VBA so need to understand where I'm going wrong

 

[Bob Phillips]

Not tested, but this has a lot of the errors, extraneous stuff removed

Sub Import()
Dim SourceWB As Workbook
Dim DestinationWB As Workbook
Dim DestinationWS As Worksheet
Dim FileName As Workbook
Dim iLstRow As Long

Set DestinationWB = ThisWorkbook 'This file
Set DestinationWS = DestinationWB.Worksheets("Import") 'This file Sheet
Import"
Set FileName = ThisWorkbook.Worksheets("Data").Range("D10") 'contents of
this file, sheet "data", Cell D10

'Open the file named in cell D10 on on sheet "data"
Set SourceWB = Workbooks.Open(FileName:=FileName, UpdateLinks:=0)
HiddenCells = Range("AV3").Value
ActiveSheet.Unprotect Password:=HiddenCells 'Unprotect Sheet
iLastRow = Range("AG72").End(xlDown).Row
Range("B2").Resize(iLastRow - 1, 31).Copy _
Destination:=DestinationWS.Range("A2").End(xlDown).Offset(1, 0)
Application.CutCopyMode = False
SourceWB.Protect Password:=HiddenCells, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True 'Reprotect Workbook
SourceWB.Save
SourceWB.Close
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Karen McKenzie]

Thanks Bob

Setting the filename is still causing errors.

The following row is causing a runtime error 13 - Type Mismatch

Set FileName = ThisWorkbook.Worksheets("Data").Range("D10")

Also can you recommend a good publication to help me learn VBA?

 

[Susan]

regarding variables, instead of writing out the ranges each time you
want to do something with them, you assign a variable to them. you
can tell excel to figure out the range for you. for instance, in this
section of code:

Range("AG72:B72").Select
Range(Selection, Selection.End(xlDown)).Select 'Select rows from row 72
down which has data in column AG
Selection.Copy

you never know if you've added more rows & perhaps 72 is not the final
row anymore. so instead, you can let excel find it for you. going
from the bottom UP is better than going xlDown because if there's any
blanks, it will stop @ the first blank.

dim myRange as range
dim myRow as integer

myRow = worksheet.cells(5000,2).end(xlup).offset(1,0).row

so it goes to row 5000 in column b, moves up to the 1st populated
cell, and then offsets one row back downward to find the first blank
row.

you know your range always goes from column b to column ag. therefore
you can tell excel to make your range as follows:

set myRange = worksheet.range("b:ag" & myRow)

once you have the variable declared & set, you can do things with it,
without selecting it:

myRange.CopyDestination:=DestinationWB.DestinationWS.Range("A2")

this range you could also qualify with another variable & set above.
you've got the right idea with dim'ing the workbooks by variable
names. you need a SourceWS though, too..... then a rDest (destination
range) & a rSource (source range - which would be myRange above).
hope i'm not turning you around in circles!

susan

 

[Bob Phillips]

Sorry, missed that one, it is not an object, so it should be

FileName = ThisWorkbook.Worksheets("Data").Range("D10").value

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[merjet]

There are multiple problems with your code. Rather than detailing
them, here is some code to get you started.

Sub CopyStuff()
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook
wbName = wb1.Worksheets("Data").Range("d10")
Set wb2 = Workbooks.Open(wbName)
wb2.Sheets("Sheet2").Range("A110").Copy _
Destination:=wb1.Sheets("Import").Range("A2")
wb2.Close
End Sub

Hth,
Merjet