VBA Import cells from a closed workbook with an offset :)

[kansfan2001]

Sub vbaimport()
' Defines variables
Dim wb1 As Workbook, wb2 As Workbook

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which
Set wb1 = ThisWorkbook
ChDir "C:\"
FName = Application.GetOpenFilename
If FName <> False Then
Set wb2 = Workbooks.Open(FName)
End If

' Import range A1:A5 from Sheet1 of wb2
wb1.Sheets("VBAImport").Range("A1:A5").Value2 = wb2.Sheets("Sheet1").Range("A1:A5").Value2


' Close wb2
wb2.Close


' Re-enable screen updating
Application.ScreenUpdating = True

End Sub



Hi,

I hope someone will answer this... I am looking to have wb1 dump the copied information at the bottom of a list but am having some issues getting that done.

I have tried to include an offset but it did not work. All I am looking to do is have this select column b and add what's copied to the next cell that is open. My header starts on B5 which is my constant and everything would be pasted below that point.

wb1.Sheets("VBAImport").Range("A1:A5").Value2 = wb2.Sheets("Sheet1").Range("A1:A5").Value2

Thanks a bunch,
Tim G

If I can get this fixed I have two more questions LOL

 

[kansfan2001]

wb1.Sheets("Player Picks").Range("B" & Cells.Rows.Count).End(xlUp).Select.Value2 = wb2.Sheets("Player Picks").Range("B6:AA6").Value2

Here's what I have come up with.... but does not work

 

[MARK858]

Try (untested)....

With wb2.Sheets("Player Picks").Range("B6:AA6")
wb1.Sheets("Player Picks").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With

 

[kansfan2001]

Mark,
Sorry I did not put my code inside code tags. I wasn't exactly sure how to do that. But before I post anything else I will get that figured out so its not confusing

Thanks for the help it works perfectly! YOU ROCK!

 

[kansfan2001]

Sub Import()
' Defines variables
Dim wb1 As Workbook, wb2 As Workbook

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which
Set wb1 = ThisWorkbook
 ChDir "C:\"
  FName = Application.GetOpenFilename
  If FName <> False Then
    Set wb2 = Workbooks.Open(FName)
  End If

' Import range B6:AA6 from Player Picks of wb2
With wb2.Sheets("Player Picks").Range("B6:AA6")
wb1.Sheets("Player Picks").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With


' Close wb2
wb2.Close


' Re-enable screen updating
Application.ScreenUpdating = True

End Sub

Here is the full code and I now know how to put my code in properly thanks again

 

[MARK858]

Happy it helped and welcome to the board.

 

[kansfan2001]

Hi Mark, I have been using this code since you helped me out with it..... but I have one tweak I would be soooooooo grateful for....... how can I make the code below work for all sheets in the folder instead of having to individually loading each file from the folder? Any thoughts would be greatly appreciated.

Sub Import()
' Defines variables
Dim wb1 As Workbook, wb2 As Workbook

' Disable screen updating to reduce screen flicker
Application.ScreenUpdating = False

' Define which workbook is which
Set wb1 = ThisWorkbook
 ChDir "C:\"
  FName = Application.GetOpenFilename
  If FName <> False Then
    Set wb2 = Workbooks.Open(FName)
  End If

' Import range B6:AA6 from Player Picks of wb2
With wb2.Sheets("Player Picks").Range("B6:AA6")
wb1.Sheets("Player Picks").Range("B" & Rows.Count).End(xlUp)(2).Resize(.Rows.Count, .Columns.Count).Value2 = .Value2
End With


' Close wb2
wb2.Close


' Re-enable screen updating
Application.ScreenUpdating = True

End Sub