Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to copy and paste
What im trying to do is this. Have a macro run that opens imports a selected
file, then have it copy and paste into a set cell range of another sheet. I have it working so that i can select a file and put the file on a new sheet in my template workbook. Im having problems geting all the cells that have data. So what i need is something to count the row that have data. I want to skip the first row since it has headers. this is the code I have so far. Dim CurWks As Worksheet Dim myWkbk As Workbook Dim myFileName As Variant Dim LastRow As Long Dim rng As Range Dim rng1 As Range Sheets.Add ' add a blank sheet Set CurWks = ActiveSheet 'or whatever you want it to be 'code to get the name and open the .csv file CurWks.Name = "Import" myFileName = Application.GetOpenFilename(filefilter:="xls Files, *.xls", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.Open Filename:=myFileName '....rest of recorded code here! Set myWkbk = ActiveWorkbook myWkbk.Worksheets(1).UsedRange.Copy _ Destination:=CurWks.Range("a1") '(Paste into A1 of the original sheet????) 'close the .csv file without making changes myWkbk.Close savechanges:=False Set rng = CurWks.Cells(Rows.Count, 2).End(xlUp)(2) ActiveWindow.SelectedSheets.Delete -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to copy and paste
This code may help
Sub xyz() Set abc = ActiveSheet usedRow = abc.UsedRange.Row usedCol = abc.UsedRange.Column End Sub "jln via OfficeKB.com" wrote: What im trying to do is this. Have a macro run that opens imports a selected file, then have it copy and paste into a set cell range of another sheet. I have it working so that i can select a file and put the file on a new sheet in my template workbook. Im having problems geting all the cells that have data. So what i need is something to count the row that have data. I want to skip the first row since it has headers. this is the code I have so far. Dim CurWks As Worksheet Dim myWkbk As Workbook Dim myFileName As Variant Dim LastRow As Long Dim rng As Range Dim rng1 As Range Sheets.Add ' add a blank sheet Set CurWks = ActiveSheet 'or whatever you want it to be 'code to get the name and open the .csv file CurWks.Name = "Import" myFileName = Application.GetOpenFilename(filefilter:="xls Files, *.xls", _ Title:="Pick a File") If myFileName = False Then MsgBox "Ok, try later" 'user hit cancel Exit Sub End If Workbooks.Open Filename:=myFileName '....rest of recorded code here! Set myWkbk = ActiveWorkbook myWkbk.Worksheets(1).UsedRange.Copy _ Destination:=CurWks.Range("a1") '(Paste into A1 of the original sheet????) 'close the .csv file without making changes myWkbk.Close savechanges:=False Set rng = CurWks.Cells(Rows.Count, 2).End(xlUp)(2) ActiveWindow.SelectedSheets.Delete -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200704/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP with macro for copy and paste | Excel Discussion (Misc queries) | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
Macro to Copy/Paste then Paste to Next Line | Excel Programming |