OzGrid

How to copy/paste between workbooks with relative referencing

Requirement:

The user is trying to create a macro wherein it copies a specific contents of a cell in one workbook and pastes into another sheet in another workbook, because the source spreadsheet changes, my macro is independent of the work book name.

The question the user has is after it copies into the JD file (See code below) the user wants it to automatically move to the next cell. When the user runs the macro again for a different source, the user wants it to continue from the next cell the user has offset to rather than the initial G2 cell.


Here is my code:

Sub Macro1()
Dim wbkname As String


wbkname = ActiveWorkbook.Name




Windows(wbkname).Activate
Range("O6:P6").Select
Selection.Copy
Windows("JD.xlsx").Activate
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(wbkname).Activate
Range("O7:P7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("JD.xlsx").Activate
Range("h2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, -1).Range("A1").Select


End Sub

Solution:

Assuming nothing else is in column G "underneath" the last cell, then this might work...

Sub Macro1()
    Dim wbkname As String
          
    wbkname = ActiveWorkbook.Name
     
     
    Windows(wbkname).Activate
    Range("O6:P6").Select
    Selection.Copy
    Windows("JD.xlsx").Activate
    Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Windows(wbkname).Activate
    Range("O7:P7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("JD.xlsx").Activate
    Range("h2").Select
     
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(1, -1).Range("A1").Select
     
     
End Sub

NB - looks like you used the macro recorder, which is great to get started with coding, but there are more efficient ways to do this, without using .activate and .select

Obtained from the OzGrid Help Forum.

Solution provided by Ger Plante.

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

See also:

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions. En conséquence, si vous utilisez actuellement Kamagra oral jelly , ou si toi-même souffrez de dysfonction érectile ensuite qui vous prévoyez en même temps que commencer à utiliser cela Kamagra, allez en rayure puis achetez du Kamagra à somme réduit.

Gallery