CODEWITHSUNDEEP
excelvbacopy
Chas
Chas

Reputation: 107

Copy One Excel Sheet To Second Sheet By Sheet Index

  1. I want to copy from one Excel sheet to another in the same workbook using reference to the active sheet, not sheet name.
  2. I want to refer to the destination range by a variable.

I have not found any examples of this.

  1. I have tried setting

currentSheet = ActiveSheet.Name,

in the line

Worksheets(currentSheet).Range("A1").Copy Worksheets("Report").Range("A1") with "currentSheet" and currentSheet.

  1. I have the following to find the next empty row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
printRow = LastRow + 2

Likewise, I have tried setting .Range("A1") to .Range("PrintRow") and .Range(PrintRow).

Sub CopyAndPrintToReport()
    'Find the last used row in a Column: column A in this example
     Dim LastRow As Long
     Dim printRow As Long
     Dim currentSheet As String
     currentSheet = ActiveSheet.Name

     Worksheets("Report").Activate
     With ActiveSheet
         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
     End With

     printRow = LastRow + 2    

     Worksheets(currentSheet).Range("A1").Copy_ 
         Worksheets("Report").Range(printRow)

End Sub

I need to refer to the active sheet by currentSheet = ActiveSheet.Name and the destination range by printRow.

Thanks for your help.

Upvotes: 0

Views: 38

Answers (1)

FAB
FAB

Reputation: 2569

If you set a variable, you do not need to use the double quotes to refer to it (it won't work).

Also while you can store and use the name of the sheet in a variable, is generally better to declare the sheet itself as a variable and use that (or the very least, in this case for sure).

Sub CopyAndPrintToReport()    
     Dim LastRow As Long
     Dim printRow As Range
     Dim currentSheet As Worksheet
     Set currentSheet = ActiveSheet

     With Worksheets("Report")
         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
         Set printRow = .Range(.Cells(LastRow + 2, "A"), .Cells(LastRow + 2, "A"))
         'Set printRow = .Cells(LastRow + 2, "A")
         'Set printRow = .Range("A" & LastRow + 2)
     End With

     currentSheet.Range("A1").Copy printRow
End Sub

Might worth renaming the variables into something like sourceSheet instead of currentSheet, but that comes with time.

I've set printRow to my way of selecting a range (bigger than just one cell), but see in the comments alternatives of doing the same thing, for a single cell.

Please let me know if i can assist further.

Upvotes: 1

Related Questions