Running Macro from another sheet but referring to Active Worksheet

**RUTrading** · 10-03-2014, 10:02 PM

Hi,

This is day 3 of me learning VBA and I am trying to learn it by doing practical work.

So I started writing a macro for a sheet called "MealCal", once I had it working on that sheet, I then decided I needed to run it from another sheet called "Meal Schedule" because that sheet will pull the data results from the sheet called "MealCal" via vlookup and give us a nice summary.

MY problem is when I started running it from the sheet "Meal Schedule" it does not paste the first row of Sheets("14 Room 09-14") into A1 of sheet "MealCal" instead is pastes it in from row A6 down and the next column again starts from row B6.

BUT when I run the macro from "MealCal" it starts at the top at A1.

I have got a problem with understanding referring to active sheet yet working from another sheet, Please could you help me with the code below. The code starts pasting in row6 of sheet "MealCal" from the second last line in the below code.

Sub MealCal()
Dim lastrow As Long
Dim LastCol As Long
Dim srcLastRow As Long
Dim Finalrow As Long

 Set currentsheet = Worksheets("MealCal")

    lastrow = Range("A" & currentsheet.Rows.Count).End(xlUp).Row
    LastCol = Range("A1").End(xlToRight).Column

    ' find out how many rows there are in the source sheet
    srcLastRow = Sheets("14 Room 09-14").Range("D" & Sheets("14 Room 09-14").Rows.Count).End(xlUp).Row
    
    ' copy from the 14 room sheet to the currentSheet "mealcal" in the range specified
    Sheets("14 Room 09-14").Range("D1:" & "D" & srcLastRow).Copy Destination:=currentsheet.Range("A" & lastrow)
    Sheets("14 Room 09-14").Range("F1").Copy Destination:=currentsheet.Range("B" & lastrow)

**JBeaucaire** · 10-03-2014, 11:01 PM

This is just meant to demonstrate the connecting of sheet names to commands. You should connect ALL commands to worksheets, by name or by variable, keeps many a bad things from happening when you do.

Option Explicit

Sub MealCal()
Dim NextRow As Long, LastCol As Long, srcLastRow As Long
Dim Finalrow As Long
Dim wsSRC As Worksheet, wsCURR As Worksheet

'set references to your two sheets for use in the rest of the macro
    Set wsCURR = ThisWorkbook.Sheets("MealCal")
    Set wsSRC = ActiveSheet

'find next empty row on current sheet
    NextRow = wsCURR.Range("A" & Rows.Count).End(xlUp).Row + 1
'find last used column from row 1
    LastCol = wsCURR.Range("A" & Columns.Count).End(xlToLeft).Column

' find out how many rows there are in the source sheet
    srcLastRow = wsSRC.Range("D" & Rows.Count).End(xlUp).Row
    
' copy from the source sheet to the currentSheet "mealcal" in the range specified
    wsSRC.Range("D1:D" & srcLastRow).Copy wscur.Range("A" & NextRow)
    wsSRC.Range("F1").Copy wsCURR.Range("B" & NextRow)

**RUTrading** · 10-04-2014, 03:22 AM

Hi, JBeaucaire

Thanks so much for the help, my macro is now working properly for your above help.

**JBeaucaire** · 10-04-2014, 12:09 PM

Awesome! Thanks for letting me know, and for the reputation feedback.

**RUTrading** · 10-06-2014, 06:17 PM

Hi JBeaucaire,

I have just run into a problem with this code which you gave me earlier, I am needing to copy the rows to the other sheet but it needs to be paste as values. How would I change the below code to make it paste as a value? When I paste Selection.PasteSpecial Paste:=xlPasteValues at then end of the below code it just gives me errors.

' copy from the source sheet to the currentSheet "mealcal" in the range specified
    wsSRC.Range("D1:D" & srcLastRow).Copy wscur.Range("A" & NextRow)

**JBeaucaire** · 10-06-2014, 06:18 PM

It takes two lines of code to do that:

' copy from the source sheet to the currentSheet "mealcal" in the range specified
    wsSRC.Range("D1:D" & srcLastRow).Copy 
    wscur.Range("A" & NextRow).PasteSpecial xlPasteValues

**RUTrading** · 10-06-2014, 06:27 PM

Works perfectly, thanks a mil.

Running Macro from another sheet but referring to Active Worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Running Macro from another sheet but referring to Active Worksheet

Re: Running Macro from another sheet but referring to Active Worksheet

Re: Running Macro from another sheet but referring to Active Worksheet

Re: Running Macro from another sheet but referring to Active Worksheet

Re: Running Macro from another sheet but referring to Active Worksheet

Re: Running Macro from another sheet but referring to Active Worksheet

Re: Running Macro from another sheet but referring to Active Worksheet

Thread Information

Users Browsing this Thread

Similar Threads

Make a macro run on only a specified worksheet regardless of which sheet is active.

Referring to active sheet

[SOLVED] Active Sheet not automatically running macro

Referring to active sheet

[SOLVED] Macro running on active sheet instead of sheet defined in macro

Bookmarks

Bookmarks

Posting Permissions