Using With to import data from other spreadsheet

[bren25m]

Hey all,

Need some advice/help. Conceptually, it is not too difficult, it's just a matter of straightening out the code a little bit.

I've created a file that will serve as a master workbook; it has a basic format of headings for columns A to BB (1 to 54) such as date, velocity, averages, etc.
I need the macro to run from this master workbook and import certain cells from another workbook (labeled as Yuma in my code) into a specific format. The data must also be imported to the next available row, since there are a lot of data files. I am new to excel macros but have done most of it. My debugger is giving me issues in the third line of my With function where I begin the commands for importing all the data. My code is below. In the With function, I've only listed two data import commands as samples (really, there are about 100 but they're all identical). Please provide me with any insight you can. Thanks.


Sub macro1()

Dim OpenFileName As String
Dim Yuma As Workbook
OpenFileName = Application.GetOpenFileName()
If OpenFileName = "False" Then Exit Sub
Set Yuma = Workbooks.Open(OpenFileName)

Dim Master As Worksheet
Set Master = ThisWorkbook.Sheets(1)

With Master
Dim irow As Long
irow = Range("A" & .Rows.Count).End(xlUp).Row + 1

.Range(Cells(irow, "A"), Cells(irow + 21, "A")).Value = Yuma.Sheets(1).Range("A9").Value
.Range(Cells(irow, "AB"), Cells(irow + 21, "AB")).Value = Yuma.Sheets(1).Range("A17, A18, A20:A24, A31:A35, A42:A46, A53:A57").Value

End With

Yuma.Close
MsgBox("Import Successful")

End Sub

 

[VoG]

Try

With Master
Dim irow As Long
    irow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    
    .Range(.Cells(irow, "A"), .Cells(irow + 21, "A")).Value = Yuma.Sheets(1).Range("A9").Value
    .Range(.Cells(irow, "AB"), .Cells(irow + 21, "AB")).Value = Yuma.Sheets(1).Range("A17, A18, A20:A24, A31:A35, A42:A46, A53:A57").Value
    
End With

 

[bren25m]

Worked perfectly, thanks Peter.
I did not realize the "." was needed anywhere else besides in front of the command.

 

[bren25m]

Actually I am still struggling with one key part. It appears that only the first row of data from the Yuma sheet (A17) was imported 22 times, instead of the range of rows A17, A18, A20:A24, A31:A35, A42:A46, A53:A57 being imported once each.

Any reason for why this is? Do I need a for loop or something? Please let me know what you think.

 

[VoG]

You can try this - if unsuccessful you will need to loop

.Range(.Cells(irow, "AB"), .Cells(irow + 21, "AB")).Value = Application.Transpose(Application.Transpose(Yuma.Sheets(1).Range("A17, A18, A20:A24, A31:A35, A42:A46, A53:A57")))

 

[bren25m]

This change resulted in #VALUE! in that column. Should I had a .Value or make some other minor change? I'm not 100% confident in my looping ability so that would be more of a last resort.
Thanks

 

[VoG]

Try

For Each c In Yuma.Sheets(1).Range("A17, A18, A20:A24, A31:A35, A42:A46, A53:A57")
    .Range(.Cells(irow, "AB")).Value = c.Value
    irow = irow + 1
Next c