Copy Paste Data from one Worksheet to Another using macros

Closed
Nana - Oct 12, 2016 at 03:51 PM
 Blocked Profile - Oct 12, 2016 at 05:53 PM
Hello,

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
'
Sheets("MASTER").Select
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
'
ActiveWorkbook.Unprotect
Sheets("SPONSOR FORM").Select
Sheets("MASTER").Visible = True
End Sub
Sub Macro3()
'
' Macro3 Macro
' Copy Paste data to Master File
'
' Keyboard Shortcut: Ctrl+t
'
Range("A3:K52").Select
Selection.Copy
Windows("IMSI MASTER FILE.xlsm").Activate
ActiveWindow.SmallScroll Down:=87
Range("A2").Select
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 response

Blocked Profile
Oct 12, 2016 at 05:53 PM
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.
0