VBA Copy/Paste

[JHusk]

I'm looking to copy/paste within the same macro, using 2 different methods, ActiveSheet.Paste and Selection.PasteSpecial.

Scenario: Row 1 has a series of cells that I'd like to duplicate to rows 2, 3, 4, etc. However some cells (call it A1) has a formula in it that I'd like to only copy the value from and not the actual formula. Then B1 has a validation list in it that id like to copy/paste into B2. And then C1 has a VLOOKUP formula in it that needs to copied/pasted into C2 to work along with B2.

So A1 would use Selection.PasteSpecial and B1 & C2 would use ActiveSheet.Paste but here's the kicker, I need to put everything in the next available row. This wouldn't be an issue except that columns B & C won't always have "results" in them. Column A will always have some type of value in it but not B & C.

Here's an example of the code I've been working with. Imagine (for the purposes of the above description) DJ23 > AD & EK24 > AG are Column A. EU24:GF24 contain the data validation and VLOOKUP formulas.

Sub NewAM()
 With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
Range("DJ23").Select
Selection.Copy
 Range("AD" & Rows.Count).End(xlUp).Offset(1).Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Range("EK24").Select
Selection.Copy
 Range("AG" & Rows.Count).End(xlUp).Offset(1).Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


'Range("EU24:GF24").Select
'Selection.Copy
'Range("AG" & Rows.Count).End(xlUp).Offset(1).Select
     'ActiveSheet.Paste
     

 With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[DanteAmor]

Try this:

Sub Test_LastRow()
  Dim f As Range
  Dim lr As Long
 
  'Find the last row with data considering several columns.
  Set f = Range("AD:AD, AG:AG, AH:AH").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
 
  If Not f Is Nothing Then
    'Next empty row of last row with data
    lr = f.Row + 1
    'Copy values:
      Range("DJ23").Copy
      Range("AD" & lr).PasteSpecial xlPasteValues
     
    'Or you can pass the value as follows
      Range("AG" & lr).Value = Range("EK24").Value
   
    'Copy everything, validations, formulas, formats, etc.
      Range("EU24:GF24").Copy Range("AH" & lr)       'Fit AH by destination column
 
  End If
End Sub

 

[JHusk]

Try this:

Sub Test_LastRow()
  Dim f As Range
  Dim lr As Long
 
  'Find the last row with data considering several columns.
  Set f = Range("AD:AD, AG:AG, AH:AH").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
 
  If Not f Is Nothing Then
    'Next empty row of last row with data
    lr = f.Row + 1
    'Copy values:
      Range("DJ23").Copy
      Range("AD" & lr).PasteSpecial xlPasteValues
    
    'Or you can pass the value as follows
      Range("AG" & lr).Value = Range("EK24").Value
  
    'Copy everything, validations, formulas, formats, etc.
      Range("EU24:GF24").Copy Range("AH" & lr)       'Fit AH by destination column
 
  End If
End Sub

No doubt it's something I'm doing wrong here but I can't get any part of this to work. Quite literally copy/pasted into Developer, ran the macro and nothing happens. Nothing is copied to the clipboard, nothing is pasted, nothing is selected.

 

[Alex Blakenburg]

My testing would indicate that the find line will only return the last cell in column AH.
You said one column always has data in it, if you change the find line to just look at that column does Dante's code do what you need ?
eg assuming column AD is always populated to the last row change the line to:

  Set f = Range("AD:AD").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)

 

[DanteAmor]

Point number 1, You must verify that you actually have data in the cells: "DJ23", "EK24", "EK24", that way the macro will be able to copy something.

Point number 2, The macro assumes that you have data in the columns "AD:AD, AG:AG, AH:AH", the macro assumes that you have at least one header, if you don't have data then the macro doesn't copy.
Or the macro is copying to a record far below and you're not seeing it.
But don't worry, if you don't have data in those columns or you have records in the sheet far below, the following code will send you a message indicating which row the data was copied to.

Secure point number 1, copy all the code and run it.

Sub Test_LastRow()
  Dim f As Range
  Dim lr As Long
  
  'Find the last row with data considering AD to AH
  Set f = Range("AD:AH").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  
  If Not f Is Nothing Then
    lr = f.Row + 1
  Else
    lr = 1
  End If
  
  'Copy values:
    Range("DJ23").Copy
    Range("AD" & lr).PasteSpecial xlPasteValues
    
  'Or you can pass the value as follows
    Range("AG" & lr).Value = Range("EK24").Value
  
  'Copy everything, validations, formulas, formats, etc.
    Range("EU24:GF24").Copy Range("AH" & lr)       'Fit AH by destination column
    
  MsgBox "The data was copied to row number: " & lr
End Sub

 

[JHusk]

Point number 1, You must verify that you actually have data in the cells: "DJ23", "EK24", "EK24", that way the macro will be able to copy something.

Point number 2, The macro assumes that you have data in the columns "AD:AD, AG:AG, AH:AH", the macro assumes that you have at least one header, if you don't have data then the macro doesn't copy.
Or the macro is copying to a record far below and you're not seeing it.
But don't worry, if you don't have data in those columns or you have records in the sheet far below, the following code will send you a message indicating which row the data was copied to.

Secure point number 1, copy all the code and run it.

Sub Test_LastRow()
  Dim f As Range
  Dim lr As Long
 
  'Find the last row with data considering AD to AH
  Set f = Range("AD:AH").Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
 
  If Not f Is Nothing Then
    lr = f.Row + 1
  Else
    lr = 1
  End If
 
  'Copy values:
    Range("DJ23").Copy
    Range("AD" & lr).PasteSpecial xlPasteValues
   
  'Or you can pass the value as follows
    Range("AG" & lr).Value = Range("EK24").Value
 
  'Copy everything, validations, formulas, formats, etc.
    Range("EU24:GF24").Copy Range("AH" & lr)       'Fit AH by destination column
   
  MsgBox "The data was copied to row number: " & lr
End Sub

So, I can't manage to get this to work with my worksheet, most likely my fault. But I was thinking about a possible workaround that may be a little less complicated.

Column AD will always have some value in it. So what about look for the last row in Column AD (call it AD5) with data, then ActiveSheet.Paste to the 4th column the right of AD5??

 

[JHusk]

@DanteAmor

I want to first say I am so grateful for all of your assistance. Your codes have been a great help. And usually force me to evaluate how I'm doing certain things. But I just couldn't get what you're sharing to work for what I need.

Turns out I had all of the coding necessary to do what I needed all along. Just needed to adjuster the .Offset to (0,10) and like magic, it works exactly as I need it to.

Sub NewAM() 'New Air Mover in Equipment Tab
 With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
Range("DJ23").Copy 'Invisible Marker
 Range("AD" & Rows.Count).End(xlUp).Offset(1).Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Range("EK24").Copy 'Air Mover Number
Range("AG" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Selection.InsertIndent 1
        
Range("EU24:GF24").Copy
Range("AG" & Columns.Count).End(xlUp).Offset(0, 10).Select
     ActiveSheet.Paste
     
 Application.CutCopyMode = False

 With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub