excel VBA to select and paste issue

Question

I was having issue in the line

current.Worksheets("Sheet1").Range("A14").Select

I dont know what i did wrong and even to paste is also problem currently.

Sub copyall()

Dim lastrow As Long

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
MsgBox (lastrow)

Dim source As Workbook
Dim current As Workbook
Dim x As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False


For i = 1 To lastrow

    x = Sheets("Sheet1").Cells(i, 1)


    Set source = Workbooks.Open(x)
    Set current = ThisWorkbook
    'source.Worksheets("Adjustment").Columns.EntireColumn.Hidden = False
    'source.Worksheets("Adjustment").Rows.EntireRow.Hidden = False

    Dim f As Range
    Set f = source.Worksheets("Adjustment").Cells.Find(what:="Adjustment Type", lookat:=xlWhole, MatchCase:=True)

    Dim lastrow_source As Integer
    y = source.Worksheets("Adjustment").Cells(f.Row + 1, f.Column)

    lastrow_source = Sheets("Adjustment").Cells(Rows.Count, 1).End(xlUp).Row
    MsgBox (y)
    source.Worksheets("Adjustment").Range(source.Worksheets("Adjustment").Cells(f.Row + 1, f.Column), Cells(lastrow_source, 23)).Select
    Application.CutCopyMode = False
    Selection.Copy
    current.Worksheets("Sheet1").Range("A14").Select
    ActiveSheet.Paste
    source.Close
    MsgBox ("Imported")
Next i

End Sub

try with Selection.PasteSpecial Paste:=xlPasteValues instead of Activesheet.Paste — , Jul 03 '18 at 07:42
Have a look about avoiding select, see this q & a as just one of many : https://stackoverflow.com/q/38833596/4961700 — Solar Mike, Jul 03 '18 at 07:48

score 0 · Answer 1 · answered Jul 03 '18 at 08:03

0

I would start by naming all the worksheets on the Project window in VBA. You have the window on your left. Click on the sheet and name it. Then you can call it without using Sheets("") or Worksheets("").

To copy paste in your case I would use this:

NameSheet1.Range("A14").Copy _ destination:= NameSheet2.Range("Input Range")

Let me know if that works.

answered Jul 03 '18 at 08:03

madrac

114
1
9

I was having a lot of files to copy from that why have to name it worksheet but for my earlier line, the current does work correctly but after copy not working anymore – terry Jul 03 '18 at 08:09

score 0 · Accepted Answer · answered Jul 03 '18 at 08:38

0

source.Worksheets("Adjustment").Range(source.Worksheets("Adjustment").Cells(f.Row + 1, 
f.Column), Cells(lastrow_source, 23)).Copy _
        Destination:=current.Sheets("HBA Billings").Range("H" & lastrow_HBA)

answered Jul 03 '18 at 08:38

terry

9
4

4

It would be great if you could add a little bit of an explanation on why and how this code provides an answer to the question. – anothernode Jul 03 '18 at 09:22

Michał Turczyn · Answer 3 · 2018-07-03T08:50:29.540

These two lines should work:

Application.CutCopyMode = False
Source.Worksheets("Adjustment").Range(Source.Worksheets("Adjustment").Cells(f.Row + 1, f.Column), Source.Worksheets("Adjustment").Cells(lastrow_source, 23)).Copy current.Worksheets("Sheet1").Range("A14")

Instead of:

source.Worksheets("Adjustment").Range(source.Worksheets("Adjustment").Cells(f.Row + 1, f.Column), Cells(lastrow_source, 23)).Select
Application.CutCopyMode = False
Selection.Copy
current.Worksheets("Sheet1").Range("A14").Select
ActiveSheet.Paste

Above way is a good example of how to avoid Select and Selection, which is very advised!

excel VBA to select and paste issue

3 Answers3