Posts by balto21218

I have a question specific to some code. I get an error on the line which reads ActiveCell.PasteSpecial, but I don't know how to correct it. To summarize what I'm trying to do: this macro opens a file, copies a range, and pastes that range - transposed - into my active worksheet.

Any recommendations for changing this would really be helpful. Thanks

Sub importMMD()


Dim Row As Integer
Dim totalrows As Integer


totalrows = ActiveSheet.UsedRange.Rows.Count


Range("a1").Select


'loop from 1 to total rows to import data
'opens (Row,1).xls and copies value into (Row,2) only if (Row,2) is blank
For Row = 1 To totalrows Step 1
If Cells(Row, 2) = "" Then
Workbooks.Open Filename:=(Cells(Row, 1).Value)
Range("C5,C6,C7,C8,C9,C11,C14,C19,C24").Select
    Range("C34").Activate
Selection.Copy
ActiveWorkbook.Close
Cells(Row, 2).Select
ActiveCell.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End If
Selection.Offset(1, 0).Select


Next Row
End Sub

Re: Syntax For File Open

for my macro, I defined row as an integer. Thing is, when I use this code, and keep the files I am opening in a local folder, the macro works correctly:

Workbooks.Open Filename:=(Cells(Row, 1).Value) & ".xls"

.

But the suggestion you gave me doesn't. I'd really prefer what you suggested, because the files I ultimately intend to reference are on a shared hard drive, for which I need to give a path.

Thanks for your prompt answer. Really appreciate the help.

I know I asked something similar to this before, but I'm really having a hard time getting the syntax correct for this line of code.

Workbooks.Open Filename:=F:MMD\Cells(Row, 1).Value) & ".xls"

Could anyone offer a correction? Thanks.

Where F:MMD is the path, and my filename comes from the cell referenced

I have a large database, and I wrote a macro to update it. However, the sheer volume of data makes it difficult to update at once (without crashing my machine), and I would like to know if there is a simple way to run the macro in increments --- say, 2,000 rows of data at once. Just wondering if these is an efficient way to code this, rather than hardcode partitions into visual basic.

Thanks for your input.

Quick question:

If I have a serial date, e.g., 37883, in Excel, how can I convert to 092007, using that exact format?

<snip by admin. Read the rules!>

Many thanks for the input!

Re: Pull Value From Closed Workbook Where Workbook Name Is In a Cell

I'm getting a range error. Here is my code.

Sub mmd_importer_2()


[g7].End(xlDown).Select
Set fillCell = ActiveCell.Offset(1, 0)
ActiveCell.End(xlToLeft).Select
Set dateCell = ActiveCell


Do While Not IsEmpty(dateCell.Offset(1, 0))
varCellvalue = dateCell.Value
    fillCell.Select
    ActiveCell.FormulaR1C1 = "='Macintosh HD:Users:ericroberts:Desktop:Master's Thesis Work:testproject:[" & varCellvalue & ".xls]Sheet1'!R1C1"
    fillCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "='Macintosh HD:Users:ericroberts:Desktop:Master's Thesis Work:testproject:[" & varCellvalue & ".xls]Sheet1'!R1C2"
    fillCell.Offset(0, 2).Select
    ActiveCell.FormulaR1C1 = "='Macintosh HD:Users:ericroberts:Desktop:Master's Thesis Work:testproject:[" & varCellvalue & ".xls]Sheet1'!R1C3"


Set dateCell = dateCell.Offset(1, 0)
Set fillCell = fillCell.Offset(1, 0)
Loop


End Sub

I know your demo code gave a starting range, but the start position on my spreadsheet is changing. Appreciate any suggestions you might have for improvement ... I've only been working with vb for about a month.

Thanks!

Re: Pull Value From Closed Workbook Where Workbook Name Is In a Cell

For your above suggestion, could you provide me with a sample? I'm not sure how I can hardcode the value of my cell into a path without running into the problem I had before.

Thanks.

Re: Pull Value From Closed Workbook Where Workbook Name Is In a Cell

Thanks for the reply. I understand the suggestion, but I don't want the name of the file to be static. I want the code I posted above to be part of a loop, so the name of the file will change with each iteration of the loop. What do you suggest I do in that case?

Thank you.

I know this question has been addressed before here, but I have a slight modification.

I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:

to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.

Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
Windows("testproject.xls").Activate
currentCell.Offset.(0,1).Select
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"

The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.

Thanks!