Multi tool use

excel VBA to select and paste issue

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


3 Answers
3

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.

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)


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!

Select

Selection

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.