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

 

[luolovepi]

I just looked at your code and found the code below is wrong, activecell is Range type.

Dim str As String
str = ActiveCell

 

[000sammy000]

Sorry this took me so long to reply to!! I understand that ActiveCell is a range however I'm trying to get the vba macro to acknowledge it as a string so that the concatenation in the cell can be read as a file name and hence opened in the Windows("str").Activate command.

Does anyone know a way of getting around this??

Thanks sooo mucH!!!

 

[Smitty]

Welcome to the Board! (in a posting capacity anyway )

You can refer the ActiveCell's value:

str = ActiveCell.Value

Then use str, but without the quotes, as with the quotes it's seen as literal text:

Workbooks(str).Activate

Note that I'd avoid the use of ActiveCell and use an explicit cell reference, as the ActiveCell might not always be what you expect it to be.

Another note is that you rarely need to use Select statements, so where you see "Select" followed by "Selection" you can generally eliminate both statements and concatenate the remaining text.

E.G.

Sheets("Parameters").Range("H2").Copy
Sheets("Payloads").Range("A2").PasteSpecial Paste:=xlPasteValues

And in that Paste Special method you don't need the other stuff as it's all default values. You only need it if you're changing the defaults.

HTH,

 

[000sammy000]

Thanks mate thats great! I'm really really enjoying testing my patience lol! But yeah, it's really frustrating when I can't get over a hurdle!! Can I please paste my code again (with a few updates) so that you can provide some words of wisdom? It's not even attempting to run a few lines of the code... basically skipping them.

What do you think?

Thanks =)

Sam

 

[Smitty]

Can I please paste my code again (with a few updates) so that you can provide some words of wisdom? It's not even attempting to run a few lines of the code... basically skipping them.

Feel free! If I can't help out then someone else sure will.

 

[000sammy000]

I went through and fixed up all of the Select and Selections as well as all of the Paste info defaults however something screwy was happening so I figured even though it's not as efficient as it could be I'd be happy as long as it worked...

It is basically doing th process where it copies the cell from Parameters sheet and pastes into Payloads sheet, offsets 11 cells down and then offsets 1 cell down on Parametrs sheet and pastes in the newly offset cell in Payloads sheet. It then repeats this process until it has done it A times. Not once have I been able to get it to acknowledge that the cell copied from Parameters is a strFileName and not a null and hence open the strFileName and copy and past the data like im trying too....

Any help or guidance would be super dooper!!!

Sub MacRun()

Sheets("Parameters").Select

Dim i As Integer
Dim A As Integer
A = Range("$I$2")

'as this is a rolling spreadsheet i want to use the ActiveCell as a string in the
Dim str As String
str = ActiveCell.Value

'first part of file path
Dim strFilePath1 As String
strFilePath1 = "Users\smyers\Desktop\VIMS_Data\"

'second part of file path
Dim strFilePath2 As String
strFilePath2 = "_Vims_Data\Summarys\"

'file name is of the format "Users\smyers\Desktop\VIMS_Data\SITENAME_Vims_Data\Summarys\str"
'where SITENAME is a fixed value located at D2 in the Parameters sheet
'AND str is a concatenated variable in the format "SITENAME_MONTH_YEAR_SUMMARY.xlsx"
Dim strFileName As String
strFileName = Dir(strFilePath1 & "$D$2" & strFilePath2 & str)

'copies the value at H2 on the parameters sheet and pastes into A2 on the Payloads sheet
Sheets("Parameters").Select
Range("H2").Select
Selection.copy
Sheets("Payloads").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'if the value pasted was a string (becuase the cell the data was being copied from was a concatenation of SITENAME_MONTH_YEAR_SUMMARY.xlsx)
'AND the cell to the right was a null string (because the rolling update has not yet updated that month eg if march had already been done
'there would be data besides SITENAME_March_2011_Summar.xlsx" and hence it would not re copy)
'THEN open the workbook of strFileName eg if March had not been done open SITENAME_March_2011_Summary.xlsx
'AND copy all the data from the Payload_Dist_Summary sheet
'THEN paste this data into the null string cell one cell right of the ActiveCell (B column) in the original Payloads sheet in the original workbook Totals.xlsm
'The macro should then step one cell back to the A column and step down 11 rows where the data pasted has 11 rows of data.
'IF the null string cell in column B had data then the activecell was to step down 11 and attempt the process again.

''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
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

'The macro should keep going back to the oringal parameters sheet in Totals.xlsm and offsetting one value until it has offset a total number of times dictated by A.
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 = vbString And ActiveCell.offset(0, 1) = vbNullString Then

Application.Workbooks.Open (strFileName)
Workbooks(strFileName).Activate
Sheets("Payload_Dist_Summary").Select
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]

Sam

What are the names of the worksheets/workbooks?

 

[tweedle]

Why does it feel like strFilePath1 is missing a drive letter?

 

[000sammy000]

Hi Norrie,

The summary workbook is called "Totals.xlsm" - this is the workbook that I'm trying to store/run the macro in.

In this workbook I have a sheet called "Parameters" and sheet called "Payloads" - I am trying to use "Parameters" as an input to generate a concatenated file name such that "Payloads" can look up the file name, open and c&p some data.

The workbook it should try and open is of the format "SITENAME_Month_Year_Summary.xlsx" (eg. SITENAME_March_2011_Summary.xlsx)

In this workbook I have a sheet called "Payload_Dist_Summary" with data always starting on cell A2.


Thanks so much guys =) =)

Sam