Copy Paste Data from one Worksheet to Another using macros [Closed]

 Blocked Profile -

I'm trying to have a macros run to copy paste from one workbook to another, however, I need the macros to recognize the last empty row to paste the new values and not overwrite them everytime I run the macros.
This is my code, would you please help me?

Sub Macro1()
' Macro1 Macro
' Hide Tabs and lock book
' Keyboard Shortcut: Ctrl+h
ActiveWindow.SelectedSheets.Visible = False
Sheets("SPONSOR FORM").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("CUSTOMER FORM").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
Sub Macro2()
' Macro2 Macro
' Unhide Tabs and Unprotect WB
' Keyboard Shortcut: Ctrl+u
Sheets("SPONSOR FORM").Select
Sheets("MASTER").Visible = True
End Sub
Sub Macro3()
' Macro3 Macro
' Copy Paste data to Master File
' Keyboard Shortcut: Ctrl+t
Windows("IMSI MASTER FILE.xlsm").Activate
ActiveWindow.SmallScroll Down:=87
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("IMSI Profile Form.xlsx").Activate
End Sub

Thanks in advance for any help given.

1 reply

Ok load a variable with the number of row counts, using a method of XLUP.

Initialize a variable as a Range, and set the last row (your first variable), to have the FOCUS (select) of the Range you created:
Dim lastrow
Dim Range
lastrow=yourworksheetname.Cells(yourworksheetname.Rows.Count, "A").END(xlUp).Row
RANGE = "A1:Z" & lastrow

that will create a range of A1 to Z and what ever your row count was returned as.

Give that a try. I am trying to Remember my last solution from work, and I can post my exact code tomorrow if needed.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!