Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Transfer Date Loop
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Transfer Date Loop
In my first worksheet, I have columns of data that I need transferred into
the second worksheet as rows (records) of data. I need to do this beginning
with Column B until there is a blank column. I am using a macro. How can I
automate this event so that only columns with data are transferred to the
second sheet?

Thank you,
Mishelley
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Transfer Date Loop

Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
www.openerconsulting.com


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need transferred
into
the second worksheet as rows (records) of data. I need to do this
beginning
with Column B until there is a blank column. I am using a macro. How
can I
automate this event so that only columns with data are transferred to
the
second sheet?

Thank you,
Mishelley


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Transfer Date Loop
Thank you for your help, Charlie. When I copy/paste your code and try to run
it, I am getting a syntax error on the following lines:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Can you help?

Thanks,
Mishelley


"Charlie" wrote:


Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
www.openerconsulting.com


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need transferred
into
the second worksheet as rows (records) of data. I need to do this
beginning
with Column B until there is a blank column. I am using a macro. How
can I
automate this event so that only columns with data are transferred to
the
second sheet?

Thank you,
Mishelley


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Transfer Date Loop

Hi,

Try this line instead and put it all on one line:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

He

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub


--------------------


Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)

Mishelley;180461 Wrote:
Thank you for your help, Charlie. When I copy/paste your code and try
to run
it, I am getting a syntax error on the following lines:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Can you help?

Thanks,
Mishelley


"Charlie" wrote:


Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need
transferred
into
the second worksheet as rows (records) of data. I need to do this
beginning
with Column B until there is a blank column. I am using a macro.
How
can I
automate this event so that only columns with data are transferred
to
the
second sheet?

Thank you,
Mishelley


--
Charlie

------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89)
View this thread: 'Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=49825)




--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Transfer Date Loop
Hi Charlie,

When I run this code, I get a Run-Time error '9':
Subscript out of range

When I go into the code, this line is highlighted in yellow:

Sheets("ColData").Select

Sorry to be a bother.

"Charlie" wrote:


Hi,

Try this line instead and put it all on one line:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

He

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub


--------------------


Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)

Mishelley;180461 Wrote:
Thank you for your help, Charlie. When I copy/paste your code and try
to run
it, I am getting a syntax error on the following lines:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Can you help?

Thanks,
Mishelley


"Charlie" wrote:


Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need
transferred
into
the second worksheet as rows (records) of data. I need to do this
beginning
with Column B until there is a blank column. I am using a macro.
How
can I
automate this event so that only columns with data are transferred
to
the
second sheet?

Thank you,
Mishelley


--
Charlie

------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89)
View this thread: 'Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=49825)




--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Transfer Date Loop

Hi,


Try this one:

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Activate

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Sheets("ColData").Cells(1, y), Sheets("ColData").Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop

Application.CutCopyMode = False
End Sub
--------------------


Mishelley;180600 Wrote:
Hi Charlie,

When I run this code, I get a Run-Time error '9':
Subscript out of range

When I go into the code, this line is highlighted in yellow:

Sheets("ColData").Select

Sorry to be a bother.

"Charlie" wrote:


Hi,

Try this line instead and put it all on one line:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

He

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub


--------------------


Charlie
'Opener Consulting Home' ('Opener Consulting Home'
(http://www.openerconsulting.com))

Mishelley;180461 Wrote:
Thank you for your help, Charlie. When I copy/paste your code and
try
to run
it, I am getting a syntax error on the following lines:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Can you help?

Thanks,
Mishelley


"Charlie" wrote:


Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go
through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
'Opener Consulting Home' ('Opener Consulting Home'
(http://www.openerconsulting.com))


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need
transferred
into
the second worksheet as rows (records) of data. I need to do
this
beginning
with Column B until there is a blank column. I am using a
macro.
How
can I
automate this event so that only columns with data are
transferred
to
the
second sheet?

Thank you,
Mishelley


--
Charlie


------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile:
Charlie'
('The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89))
View this thread: 'Transfer Date Loop - The Code Cage Forums'
('Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=49825))




--
Charlie

------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89)
View this thread: 'Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=49825)




--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6
Default Transfer Date Loop
Hi,

Same error, but now with this line.

Sheets("ColData").Activate

"Charlie" wrote:


Hi,


Try this one:

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Activate

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Sheets("ColData").Cells(1, y), Sheets("ColData").Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop

Application.CutCopyMode = False
End Sub
--------------------


Mishelley;180600 Wrote:
Hi Charlie,

When I run this code, I get a Run-Time error '9':
Subscript out of range

When I go into the code, this line is highlighted in yellow:

Sheets("ColData").Select

Sorry to be a bother.

"Charlie" wrote:


Hi,

Try this line instead and put it all on one line:
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

He

Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 2

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 0 'Go through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub


--------------------


Charlie
'Opener Consulting Home' ('Opener Consulting Home'
(http://www.openerconsulting.com))

Mishelley;180461 Wrote:
Thank you for your help, Charlie. When I copy/paste your code and
try
to run
it, I am getting a syntax error on the following lines:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Can you help?

Thanks,
Mishelley


"Charlie" wrote:


Hi,

Try this:

Sub Macro1()
Dim x As Integer
Dim y As Integer

x = 2 'First row to copy data

Sheets("ColData").Select

y = 1

Do While Len(Trim(Sheets("ColData").Cells(1, y))) 1 'Go
through
column until blank in row 1

Sheets("ColData").Select
Sheets("ColData").Range(Cells(1, y), Cells(100, y)).Select
Selection.Copy

Sheets("RowData").Select
Sheets("RowData").Cells(x, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

x = x + 1 'Next row
y = y + 1
Loop
End Sub

Charlie
'Opener Consulting Home' ('Opener Consulting Home'
(http://www.openerconsulting.com))


Mishelley;180151 Wrote:
In my first worksheet, I have columns of data that I need
transferred
into
the second worksheet as rows (records) of data. I need to do
this
beginning
with Column B until there is a blank column. I am using a
macro.
How
can I
automate this event so that only columns with data are
transferred
to
the
second sheet?

Thank you,
Mishelley


--
Charlie


------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile:
Charlie'
('The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89))
View this thread: 'Transfer Date Loop - The Code Cage Forums'
('Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=49825))




--
Charlie

------------------------------------------------------------------------
Charlie's Profile: 'The Code Cage Forums - View Profile: Charlie'
(http://www.thecodecage.com/forumz/member.php?userid=89)
View this thread: 'Transfer Date Loop - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=49825)




--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Transfer Date Loop

Mishelley;180711 Wrote:
Hi,

Same error, but now with this line.

Sheets("ColData").Activate

Sorry, I edited the post, I did some modification but you probably
got the old version:

Try this one:
And replace The "Name of the sheet..." by the name of the sheet on your
workbook (Line 6-7 of the code)



Code:
--------------------
Sub Macro1()
Dim x As Integer
Dim y As Integer
Dim SColData As String
Dim SRowData As String

SColData = "Name of the sheet with column data"
SRowData = "Name of the sheet with row data"

x = 2 'First row to copy data

Sheets(SColData).Activate

y = 2

Do While Len(Trim(Sheets(SColData).Cells(1, y))) 0 'Go through column until blank in row 1

Sheets(SColData).Select
Sheets(SColData).Range(Sheets(SColData).Cells(1, y), Sheets(SColData).Cells(100, y)).Select
Selection.Copy

Sheets(SRowData).Select
Sheets(SRowData).Cells(x, 1).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

x = x + 1 'Next row
y = y + 1

Loop

Application.CutCopyMode = False
End Sub
--------------------


Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49825

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
Improve speed of data transfer loop patrick Excel Programming 2 February 14th 08 10:04 PM
transfer date to number artisdepartis Excel Worksheet Functions 4 June 19th 07 09:30 AM
Transfer data as per there date in there month. Tiya Excel Programming 0 July 2nd 06 01:30 PM
Copy the 14 cells and transfer them to another sheet loop Crowbar via OfficeKB.com Excel Programming 5 January 9th 06 05:21 AM
Date Transfer to userform MBlake Excel Programming 5 May 11th 05 07:55 PM


All times are GMT +1. The time now is 09:08 AM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement