Excel VBA Copy/Pasting all used cells in one column to a different sheet

1
votes

I would like to copy and paste all used cells in one column on one spreadsheet to another spreadsheet. I am trying to get it done such that the report can be run many times with things being added/deleted to the first column and pasted over.

I am trying to do it with the following code, but it is giving me runtime error 91 - Object variable or With block variable not set.

Here's the code:

public Sheet1 as String, Sheet 2 as string    
Sub PolicyLanguage()

Dim Range1 As Range
Set Range1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
ThisWorkbook.Worksheets(Sheet1).Activate
Range1.Copy Destination:=wb.Sheets(Sheet2)

End Sub

Update: I was able to solve the problem relatively cleanly by using a count function to determine the number of cells in the column that contained a value, and use that number to determine my copy range. This came in handy in a lot of other sections of my macro, so I figured I'd post it in case anyone needs it. 

Dim i As Integer, n As Integer
    i = Worksheets("Sheet1").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
    n = i + 1
Worksheets("Sheet1").Range("A1:A" & CStr(n)).Copy Destination:=Sheets(NewSheetName).Range("A1:A" & CStr(n))
3
vbaexcel
The problem is wb is not defined. Instead Range1.Copy Destination:=Sheet2.Range("A1"). Also remove that superfluous ThisWorkbook.Worksheets(Sheet1).Activate Really no reason to active a worksheet here unless you really want that sheet to be selected when the code is done running. You could also do Range1.Copy Destination:=ThisWorkbook.Worksheets("Sheet2").Range("A1") or similar. – JNevill
@JNevill technically he also did not define Sheet1 or Sheet 2 – Marcucciboy2
Do you want the formulas, formatting, etc. or just the values copied? – BruceWayne
I need the formatting and values – VBAmazing

3 Answers

0
votes

This solution should work for you:

Option Explicit

Public Sheet1 As String, Sheet2 As String
Sub PolicyLanguage()
Dim Range1  As Range
Dim wb      As Workbook

Set Range1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set wb = ThisWorkbook

  wb.Sheets("Sheet1").Activate
  Range1.Copy Destination:=wb.Sheets("Sheet2").Range("A1")

End Sub


My example assumes that your worksheets are titled Sheet1 and Sheet2, so if they aren't, you'll need to adjust that accordingly (Sheet1 being where your data lives, Sheet2 being the destination you want it copied to).

That being said, you can alternatively use the codenames of the sheets instead (which is usually preferable as they are less likely to be changed by the user and can only be changed manually). This is the name that is found under (Name) in Properties in the VBE. I'm not sure what your public variables are being used for, but make sure the codenames of the two sheets in question are different.

For this example, I changed Sheet1 to SheetA and Sheet2 to SheetB:

Option Explicit

Public Sheet1 As String, Sheet2 As String
Sub PolicyLanguage()
Dim Range1  As Range
Dim wb      As Workbook

Set Range1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set wb = ThisWorkbook

  SheetA.Activate
  Range1.Copy Destination:=SheetB.Range("A1")

End Sub
0
votes

All you need to do is to wrap your code in a With statement.

Dim Range1 As Range
Set Range1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

With ThisWorkbook
    Range1.Copy Sheet2.Range("A1")
End With

If you want to keep it simple, get rid of the variable.

With ThisWorkbook
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheet2.Range("A1")
End With
-1
votes

Try the following:

Sub PolicyLanguage()

Dim Range1 As Range
Set Range1 = Worksheets("Sheet1").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
ThisWorkbook.Worksheets("Sheet1").Activate
Range1.Copy Destination:=Worksheets("Sheet2").Range("A1")

End Sub

Sheet1 and Sheet2 should be in quotations because they are strings. Defining them with a Public function as strings makes them string variables and accessible to other modules in Excel. You can read more about it in the link.

http://www.excelfunctions.net/VBA-Functions-And-Subroutines.html

I found it was not necessary to use Public. Also, in the last line wb will not work to call out your worksheet and you have to give the destination a starting range.