New to Macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In short I'm trying to set up a macro so that a spreadsheet full of data (4
or 5 colums) can be copied and pasted into a user details worksheet. Once
copied I will run the macro, which will create, name, and populate a new
worksheet in the same workbook. My first attempt is below;

Range("A3").Select
Selection.Copy
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "PID1"
Sheets("Template").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("User Details List").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("User Details List").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Sheets("User Details List").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Sheets("User Details List").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Sheets("User Details List").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Sheets("User Details List").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
ActiveWindow.SmallScroll ToRight:=1
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
End Sub

It doesn't seem to be iterative or relative to fields it has already used.
I ran the macro and it created an error.

Any help would be very much appreciated

Many Thanks

Richard
 
I've just tried to re-record the macro using the relative reference button
and got the following

ActiveCell.Offset(-2, -10).Range("A1").Select
Selection.Copy
Sheets.Add
Sheets("Sheet14").Select
Sheets("Sheet14").Name = "PID1"
Sheets("Template").Select
Range("A1:F1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Sheets("User Details List").Select
Rows("3:3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("PID1").Select
Rows("3:3").Select
ActiveSheet.Paste

I'm just wanting each entry in the first column (A), to generate a worksheet
(which will be labelled the contents in the field. Then copy the remaining
relevant data in the row to the relevant columns in the new worksheet.
 
Hi Richard,

This makes no sense to me, although I'm no expert. Looks like you are
trying to select a cell 2 rows to the left and 10 columns up from the
activecell. What is A1 doing in there?

ActiveCell.Offset(-2, -10).Range("A1").Select

Try this to clean up your code. You rarely have to select anything to get
your code to work. Although I do have trouble with Paste Special often,
seems you have to select in that case. Maybe an expert will dive in and
clear that up.

To copy a cell to another sheet. No need to select

Range("A1").Copy Sheets("Sheet2").Range("A1")

To copy a row of info to another sheet. 1 row and 5 columns wide in this
case.

Range("A1").Resize(1, 5).Copy Sheets("Sheet2").Range("A1")

HTH
Regards,
Howard
 
I got that backwards.

ActiveCell.Offset(-2, -10).Range("A1").Select

Is 2 rows up and 10 columns to the left.
 
Back
Top