PDA

View Full Version : Trouble Changing Active Cell After Pasting To 2nd Workbook



TampaStorm
08-05-2019, 07:38 AM
First of all, first time here. Used to use another site, but they had an extremely unfriendly moderator so many of us are leaving. :hi:

I'm getting in to some pretty intricate spreadsheets now which require me to learn vba. I seem to be making a rather simple problem incredibly difficult for myself. All I want to do is deselect the cells I pasted and move it to k3. I appreciate your help ahead of time. To have people so willing to help is AMAZING!!

Here's just the top part of my code. I want to insert it at the bottom of this here...


Sub CopyAUG() Dim Fname As String
Dim SrcWbk As Workbook
Dim DestWbk As Workbook

Set DestWbk = ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")
If Fname = "False" Then Exit Sub
Set SrcWbk = Workbooks.Open(Fname)

Dim answer As Integer
answer = MsgBox("Did You Pick The Correct File?", vbQuestion + vbYesNo)

If answer = vbNo Then Exit Sub


MsgBox "Your Screen May Look Frozen While Importing." & vbNewLine & vbNewLine & "Just Be Patient!", vbOKCancel + vbQuestion


'Managers
SrcWbk.Sheets("MISC").Range("F3:h16").Copy: DestWbk.Sheets("MAIN").Range("k3:m16").PasteSpecial xlPasteValues
SrcWbk.Sheets("MISC").Range("F33:f39").Copy: DestWbk.Sheets("MAIN").Range("i3:i9").PasteSpecial xlPasteValues

Paul_Hossler
08-06-2019, 07:37 AM
Welcom to the forum

Please take a minute to read the FAQs linked in my sig

If I understand the questions



Option Explicit

Sub test()
ActiveSheet.Cells(1, 1).CurrentRegion.Copy ActiveSheet.Cells(1, 15)

Application.CutCopyMode = False

Application.Goto Range("Z1")

End Sub




You may have to activate the right workbook or worksheet

Artik
08-06-2019, 02:28 PM
And if you do this without opening the source file?
Sub CopyAUG_1() Dim Fname As Variant
Dim strWksSrc As String
Dim wksDest As Worksheet
Dim answer As VbMsgBoxResult


Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Select a File")


If TypeName(Fname) = "Boolean" Then Exit Sub


answer = MsgBox("Do you want to download data from the file:" & vbLf & Fname, vbQuestion + vbYesNo)


If answer = vbNo Then Exit Sub


Fname = Split(Fname, Application.PathSeparator)
Fname(UBound(Fname)) = "[" & Fname(UBound(Fname)) & "]"
Fname = Join(Fname, Application.PathSeparator)


strWksSrc = "MISC"
Set wksDest = ThisWorkbook.Worksheets("MAIN")

Application.ScreenUpdating = False

'Managers
With wksDest
'SrcWbk.Sheets("MISC").Range("F3:h16").Copy: DestWbk.Sheets("MAIN").Range("k3:m16").PasteSpecial xlPasteValues
With .Range("K3:M16")
.Formula = "='" & Fname & strWksSrc & "'!F3"
.Value = .Value
End With


'SrcWbk.Sheets("MISC").Range("F33:f39").Copy: DestWbk.Sheets("MAIN").Range("i3:i9").PasteSpecial xlPasteValues
With wksDest.Range("I3:I9")
.Formula = "='" & Fname & strWksSrc & "'!F33"
.Value = .Value
End With
End With


End Sub
Artik