moving data

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
hi everyone,

i need a macro to copy a column (A) of data from on sheet to another sheet column A (easly done i know) - "sheet 1" to "sheet 2"

but when the macro is run again i need it to copy to the next column and so on.


can anyone help?

Thanks in advance! :biggrin:
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Joined
Jul 30, 2006
Messages
3,656
mani_singh,

Please TEST this FIRST in a COPY of your workbook.

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code:
Option Explicit
Sub CopySheet1ColumnAToSheet2NextAvailableColumn()
    Dim lngNextColumn As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        lngNextColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
    End With
    Sheets("Sheet1").Columns("A:A").Copy Sheets("Sheet2").Cells(1, lngNextColumn)
    Application.CutCopyMode = False
    Sheets("Sheet1").Range("A1").Select
    Application.ScreenUpdating = True
End Sub


Then run the 'CopySheet1ColumnAToSheet2NextAvailableColumn' macro.

Have a great day,
Stan
 

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
all i need is to be able to paste into the next empty column to the right using the macro

i think you use a limit command just not sure!

thanks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

didn't work just got some really random figures! pasted
What is in column A of Sheet1.. directly entered data ... formulas?

Try and tell us a bit more about what is happening in your sheet as Stan's code seemed to work fine for me.
 

mani_singh

Well-known Member
Joined
Jul 24, 2007
Messages
583
copying data using a macro

after a bit of fiddeling with my sheets and code its worked!

could you guys help me with one final thing please:

this data is copied into sheet 4 not sheet 2 as in the previous code

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

i need to perform a search using a macro which reads cell C3 on sheet 3

if it finds the a matching results in column A on sheet 4 it copies that row and pastes it into sheet 2

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

thank you guys i know its a pain but im loosing my mind here!

thanks again :biggrin:
 
Joined
Jul 30, 2006
Messages
3,656
mani_singh,

after a bit of fiddeling with my sheets and code its worked!

could you guys help me with one final thing please:

this data is copied into sheet 4 not sheet 2 as in the previous code


Here is the new code per your new requirements:

Code:
Option Explicit
Sub CopySheet1ColumnAToSheet4NextAvailableColumn()
    Dim lngNextColumn As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet4")
        lngNextColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
    End With
    Sheets("Sheet1").Columns("A:A").Copy Sheets("Sheet4").Cells(1, lngNextColumn)
    Application.CutCopyMode = False
    Sheets("Sheet1").Range("A1").Select
    Application.ScreenUpdating = True
End Sub


i need to perform a search using a macro which reads cell C3 on sheet 3
if it finds the a matching results in column A on sheet 4 it copies that row and pastes it into sheet 2


Please post examples of the data that can be in Sheet3, cell 'C3'.

And, please post samples of the data tha can be in the first ten rows of Sheet2.

Post a screenshot of your sheet(s) with Colo's HTML Maker.
[url]http://www.puremis.net/excel/downloads.shtml
[/url]

Have a great day,
Stan
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,487
Members
416,031
Latest member
Omega67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top