ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   New to Macros (https://www.excelbanter.com/excel-discussion-misc-queries/50175-new-macros.html)

Richard D
New to Macros
 
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

--
Just a beginner

Richard D
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.


--
Just a beginner


"Richard D" wrote:

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

--
Just a beginner

L. Howard Kittle
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

"Richard D" wrote in message
...
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

--
Just a beginner

L. Howard Kittle
I got that backwards.

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

Is 2 rows up and 10 columns to the left.

"Richard D" wrote in message
...
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

--
Just a beginner


All times are GMT +1. The time now is 05:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com