Excel VBA Macro Issue

Question

I am currently having a problem compiling a Macro that I am trying to make. I am either trying to use a Named Range or the actual range on a different sheet and copy and paste that data into the current sheet. I want the data to post to the current sheet so I can run this Macro on every day of the month. Here is what I have compiled but for some reason I cannot use the ActiveSheet. Attached is my code.

Sub Macro1()

' Daily Route Sheet

    Sheets("2").Select
    ActiveCell.Range("A1:H44").Select
    Selection.Copy
    Sheets("ActiveSheet").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

I believe you mean to have `Sheets("Sheet1").Select` but best would be to do this without selecting anything. — Matt Cremeens, Jun 22 '16 at 17:58
No, I am trying to grab information from sheet 2 - I named it "2" so our inspectors can quickly click on it. So what I am trying to do is our contractors like to copy and paste but they do not use special paste so it ruins our named ranges. I hid the ranges and I am trying to write something that copies from lets say Day 2 and can be put into Day 23 for example. I would like to copy the data from a different day and post it in the current sheet they are working in — hipster_pete, Jun 22 '16 at 18:03
And you have a sheet with name "ActiveSheet", if you don't have only need erase the line `Sheets("ActiveSheet").Select` — Juanpablomo, Jun 22 '16 at 18:06
Take a look [here](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for avoiding `Select` in your macros. — TheEngineer, Jun 22 '16 at 19:22
what is the benefit of `activesheet.select` ?! if it is already active, why would you re-active it ? — Patrick Lepelletier, Jun 23 '16 at 09:10

score 3 · Answer 1 · answered Jun 22 '16 at 18:03

3

Instead of selecting, cutting and pasting, consider

Sub Macro1()
    Worksheets("Sheet1").Range("A1:H44").Value = Worksheets("Sheet2").Range("A1:H44").Value
End Sub

answered Jun 22 '16 at 18:03

Matt Cremeens

4,951
7
38
67

Worksheets("Sheet1").Range("A1:H44") = Worksheets("Sheet2").Range("A1:H44") makes sense to me but how do I get it on the current sheet im working on. that's my problem I run into – hipster_pete Jun 22 '16 at 18:06
1

Then change `Worksheets("Sheet1").Range("A1:H44").Value` to `ActiveSheet.Range("A1:H44").Value`. Don't omit the `.Value` like I originally did. – Matt Cremeens Jun 22 '16 at 18:08
@hipster_pete - Just change the left part of the formula to refer to the sheet you're working on. If it's not "Sheet1", just change that to "mySheetName" or whatever... – BruceWayne Jun 22 '16 at 18:08
I don't want to do that because I want to run a macro that can be run on any day without having to change the posting location - Id rather have it on the current active sheet. – hipster_pete Jun 22 '16 at 18:10
1

@hipster_pete did you see my comment? – Matt Cremeens Jun 22 '16 at 18:11
@hipster_pete, So just change the left most `Worksheets("Sheet1")` to `ActiveSheet`... no? Seems straightforward. – trincot Jun 22 '16 at 18:12
@MattCremeens I am now completely lost but I did see your comment. I keep getting an error – hipster_pete Jun 22 '16 at 18:15
This is what I inputed – hipster_pete Jun 22 '16 at 18:20
Sub Macro1() Worksheets("2").Range("A1:H44").Value = Worksheets("ActiveSheet").Range("A1:H44").Value End Sub the error I get is saying it's not in range – hipster_pete Jun 22 '16 at 18:20
No, use `ActiveSheet.Range("A1:H44").Value = Worksheets("Sheet2").Range("A1:H44").Value` – Matt Cremeens Jun 22 '16 at 18:22
@MattCremeens THAT WORKED YOU ARE MY HERO <3 – hipster_pete Jun 22 '16 at 18:25
Glad to hear. All the best. – Matt Cremeens Jun 22 '16 at 18:26

score 0 · Answer 2 · answered Jun 22 '16 at 18:05

0

Change

Sheets("ActiveSheet").Select

to

ActiveSheet.Select

However, I agree with Matt on the point that you should try to stay away from .Select

answered Jun 22 '16 at 18:05

TenderShortGoldenRetriever

339
2
11

I agree with that too - I like the = part, I am getting stuck on the getting info onto the current sheet I am on – hipster_pete Jun 22 '16 at 18:09

score 0 · Answer 3 · answered Jun 23 '16 at 09:27

"ActiveSheet" is a string. Activesheet is a Worksheet Object.

Activesheet.range("A1").value is equivalent to Sheets(Activesheet.name).value or to Sheets(Activesheet.index).value.

That is because Sheets (or Worksheets, wich are the same) , can be referenced in braquets () trough either their names (being strings), or their Index (number, wich is the sheet's position in the Workbook).

Worksheetsis a collection (by collection , i mean a kind of array) of your workbook's sheets.

Activesheet is a VBA easy way to access a sheet wich is already active, in the same way that ActiveWorkbook' for workbooks, orActiveCell` for cells. They are all Objects , not Strings.

score -3 · Answer 4 · answered Jun 22 '16 at 18:26

-3

Sub Macro1() ActiveSheet.Range("A1:H44").Value = Worksheets("2").Range("A1:H44").Value End Sub

Kudos to Matt for the answer!

answered Jun 22 '16 at 18:26

hipster_pete

1

2

Please don't do that. Mark Matt's answer as accepted instead, by clicking the checkmark next to it. – Mathieu Guindon Jun 22 '16 at 18:32

Excel VBA Macro Issue

4 Answers4