Confused Workbooks("str").Activate problems

[000sammy000]

Hi all,

I've been sourcing help from MrExcel for a while now but have never really felt the confidence to post any solutions or answers myself when there are so many people out there who seem to be gurus!!! Thanks to everyone who has ever helped me!! =)

I'm currently trying to write a macro that seems to be getting a little out of control.... Nonetheless I'm getting the "Run time error "9" Subscript out of range" error at * below.

I know the actual file name that it relates to is correct however I don't know what the problem is in terms of why it wont route there. At the moment I have Dim str As String and str = ActiveCell basically in my formula above and below that point I've got the activecell to land on a cell with the file name "Site_Month_Year_Summary.xlsx" In a previous worksheet I've got a table that allows user to type in what site they are looking at, what month they are looking at adding data to the totals workbook from and what year this month is in. When the user adds this data to the "Parameters" workseet it concatenates into a another coloum. This coloum below is recognised as H with the first concatenation of "Site_Month_Year_Summary.xlsx" at H2. I've also got a cell that calculates how many rows have data in them such that the Totals.xlsm workbook can be used as a rolling summary and that everytime a new month comes around this workbook can be opened and a macro run to update the data.

The first part of the macro to * basically copies the concatenation and pastes it into the "Payloads" worksheet. The second part should be saying if there was something pasted at A2 and there is no data one cell to the left of A2 (where the concatenation was pasted) then move onto the next step. The next step says A2 is the ActiveCell and hence it is recognised as str so it should Workbooks("str").Activate which as far as I know should mean its actually reading Workbooks("Site_Month_Year_Summary.xlsx").Activate since str = ActiveCell = Site_Month_Year_Summary.xlsx ???? Is all this logic correct?? The idea then is that it copys and pastes the values from the "Payloads" sheet out of Site_Month_Year_Summary.xlsx and into the ActiveCell.Offset(0,1) in the original Totals.xlsm workbook "Payloads" sheet.

I would hope that it will then step back to the A column and step down 11 rows (as the copied data is 10 rows deep). It would then flcik back to the "Parameters" sheet and if A = Range("$I$2") = > 1 then it will step down to H3 copy the data, paste into "Payloads" if text and if data to right is null then open new workbook and copy and paste etc etc until it runs through each month taht has been updated. I get it to check if the data to the right is null so that it doesnt copy and paste data that has already been copied and pasted in the past.

Sorry for having such a rediculously long post but I'm a student doing some holiday work and this has taken me a lot longer than it should have!! It's so hard to get/keep jobs right now and this would really help the company and hence helpe me!!

Thanks so much for your help already!!!

Kind regards,

Sam

Sub Macro9()
 
Sheets("Parameters").Select
 
Dim i As Integer
Dim A As Integer
A = Range("$I$2")
Dim str As String
str = ActiveCell
 
Sheets("Parameters").Select
Range("H2").Select
Selection.copy
Sheets("Payloads").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False *:warning:
 
If Range("A2") = vbString And ActiveCell.offset(0, 1) = vbNullString Then
 
Workbooks("str").Activate *:rolleyes:
Sheets("Payloads").Select 
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
 
Workbooks("Totals.xlsm").Activate
Range("ActiveCell").Select
ActiveCell.offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.offset(0, -1).Select
ActiveCell.offset(11, 0).Select
 
Else
ActiveCell.offset(11, 0).Select
 
End If
 
For i = 1 To A
 
Sheets("Parameters").Select
ActiveCell.offset(1, 0).Select
Application.CutCopyMode = False
Selection.copy
Sheets("Payloads").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
If ActiveCell = vbNullString And ActiveCell.offset(0, 1) = vbNullString Then
 
Windows("str").Activate
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
 
Windows("Totals.xlsm").Activate
Range("ActiveCell").Select
ActiveCell.offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.offset(0, -1).Select
ActiveCell.offset(11, 0).Select
 
Else
ActiveCell.offset(11, 0).Select
  
End If
 
Next i
 
End Sub

 

[Norie]

Just to add, I think as well as the Select/Selection thing you need to have a look at unqualified references.

Without the right references to things like ranges, worksheets, workbooks there's a pretty good chance the code is going to go wrong.

That's especially important when you are working with more than workbook.


PS I don't think str is a good name for a variable, there's actually a VBA function with the name Str.

 

[000sammy000]

OK great thanks Norie. I've changed the "str" varibale to "Document" however it's still not playing ball. Its giving me a runtime error 1004 that says " can not be found. Please check spelling etc.

It's giving me the error at the "Application.Workbooks.Open (strFileName)" line below:

''I actually think its failing at the "Range("A2") = vbstring" line...
If Range("A2") = vbString And ActiveCell.offset(0, 1) = vbNullString Then

Application.Workbooks.Open (strFileName)
Workbooks(strFileName).Activate
Sheets("Payload_Dist_Summary").Select

How long have you guys been writing macros in VB and VBA for?

 

[tweedle]

try editing this

strFilePath1 = "Users\smyers\Desktop\VIMS_Data\"

to

strFilePath1 = "C:\Users\smyers\Desktop\VIMS_Data\"

[a long time]

 

[000sammy000]

Yeah thanks Tweedle. I laughed when I read your first post because that should have been something obvious! However, it didn't work... I've got the C:\ coded into my macro now but its still bouncing back. I've copied the seperate strPathName1 and strPathName2 and the D2 value and typed them into an explorer bar and the folder opens up so I don't understand the problem =(

 

[tweedle]

shall we try
debug.? strFileName
just before it's attempted to open it?
That should resove to be the full path and file name.

 

[000sammy000]

Like this??

''I actually think its failing at the "Range("A2") = vbstring" line...
If Range("A2") = vbString And ActiveCell.offset(0, 1) = vbNullString Then

Debug.Print strFileName
Application.Workbooks.Open (strFileName)

It hasn't given me any error message?

 

[tweedle]

I think you're correct:
try this and see if we get through the tests in desired fashion:

If Not IsEmpty(Range("A2")) And IsEmpty(ActiveCell.Offset(0, 1)) Then

Application.Workbooks.Open (strFileName)
etc.