VBA Code Help

[NX555V]

Hello everyone,

I have the below code that copies specific cells and drops them into a new (copied from a Master) worksheet. The sheet contains 25 rows for user input, such as customer name. I created 25 macros and just changed the row reference. Example E1, E2, E3 etc. I am positive there is a way to set the macro and it choose the adjacent cells without have to have one macro per row.

A little bit more detail - I have an image that I assign a macro for each row. So on row 3 "MacroRow3" is the assigned macro. The code (in part) for that macro is Range(E3).Copy, Range(F3).Copy, Range(Q3).Copy, the paste in new sheet, etc. If the only way is to have 1 macro per row, I am fine with that. But I want to make sure.


Can I have one macro that looks at each row without me assigning a specific macro per row.

Sub Copy_New_Sheet1()

' This macro will create a new sheet, based on the Master,
' It will take the input from the user and add it to the
' new sheet and name the new sheet

Application.ScreenUpdating = False
'Application.DisplayAlerts = False
'Application.EnableEvents = False

Sheets("Master").Visible = True

Sheets("Master").Select
Sheets("Master").Copy After:=Sheets(Sheets.Count)

'Prepared for info
Sheets("Details").Range("E37").Copy
ActiveSheet.Range("C2").PasteSpecial xlPasteValues

'Client Status info
'Sheets("Details").Range("I13").Copy
'ActiveSheet.Range("C3").PasteSpecial xlPasteValues

'Client Name info
Sheets("Details").Range("I37").Copy
ActiveSheet.Range("C3").PasteSpecial xlPasteValues

'Prepared On info
Sheets("Details").Range("Q37").Copy
ActiveSheet.Range("L2").PasteSpecial xlPasteValues

'Prepared By info
Sheets("Details").Range("U37").Copy
ActiveSheet.Range("L3").PasteSpecial xlPasteValues

'Changes the name of the sheet based on user input
ActiveSheet.Select
ActiveSheet.Name = ActiveSheet.Range("C3").Value

Sheets("LAMP Master Summary").Select
Rows("27:27").Select
Selection.EntireRow.Hidden = False

Sheets("Master").Visible = False

Application.CutCopyMode = False

'Call CopySheetName

'Application.DisplayAlerts = True
'Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Fluff]

Will you always be copying the last used row to the new sheet?

 

[NX555V]

Will you always be copying the last used row to the new sheet?

Yes

 

[Fluff]

In that case how about

Sub NX555V()
   Dim UsdRws As Long
   
   Application.ScreenUpdating = False
   'Application.DisplayAlerts = False
   'Application.EnableEvents = False

   With Sheets("Master")
      .Visible = True
      .Copy After:=Sheets(Sheets.Count)
      .Visible = False
   End With
   With Sheets("Details")
      UsdRws = .Range("E" & Rows.Count).End(xlUp).Row
      Range("C2").Value = .Range("E" & UsdRws).Value
      Range("C3").Value = .Range("I" & UsdRws).Value
      Range("L2").Value = .Range("Q" & UsdRws).Value
   End With
   ActiveSheet.Name = Range("C3").Value

   Sheets("LAMP Master Summary").Rows("27:27").Hidden = False
   'Call CopySheetName

   'Application.DisplayAlerts = True
   'Application.EnableEvents = True
End Sub