Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > New to Macros
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Richard D
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Richard D
 
Posts: n/a
Default
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
Reply With QuoteReply With Quote
  #3   Report Post  
L. Howard Kittle
 
Posts: n/a
Default
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


Reply With QuoteReply With Quote
  #4   Report Post  
L. Howard Kittle
 
Posts: n/a
Default
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect macros? JulieD Excel Discussion (Misc queries) 6 August 25th 05 08:38 PM
Saving Macros Gerald Excel Discussion (Misc queries) 1 August 19th 05 02:28 PM
Enabling macros Peter M Excel Discussion (Misc queries) 3 February 7th 05 10:57 PM
sorting with macros Sorting in macros Excel Discussion (Misc queries) 1 February 1st 05 09:02 AM
Transferring toolbars and macros to other computers Darrell Excel Discussion (Misc queries) 1 January 19th 05 12:21 AM


All times are GMT +1. The time now is 10:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement