VBA:循环从主页复制某些列,创建一个新的工作表并粘贴 [英] VBA: loop to copy certain columns from main sheet, create a new sheet, and paste
问题描述
表格(1)。激活
范围(A1:A14 )。选择
'这是我需要复制下一列,并且每次代码循环
时增加$ SelectionCopy
Sheets.Add After:= Sheets(Sheets.Count)
ActiveSheet.Paste
帮助将不胜感激。谢谢。
你最好的办法是在你的Range()里面使用Cells(),以循环遍历每一列。这个代码应该可以帮助你:
Sub columnCopy()
Dim sh1 As Worksheet
设置sh1 = sheet(Sheet1)
lc = sh1.Cells(1,Columns.Count).End(xlToLeft).Column'最后一列
对于i = 2 To lc
sh1.Range(sh1.Cells(1,1),sh1.Cells(12,1))。复制
Worksheets.Add
sheet (1).Cells(1,1).PasteSpecial Paste:= xlPasteValues
sh1.Range(sh1.Cells(1,i),sh1.Cells(12,i))。 $ b sheet(1).Cells(1,2).PasteSpecial Paste:= xlPasteValues
Next i
End Sub
I have a sheet with a specific number columns. I need to copy columns A and another column starting with column B, create a new sheet and paste those columns there. I then want to loop it so that it copies columns A and C this time, then create a new sheet and paste, and so forth until it reaches the last column on the main sheet. Column A is fixed so that it is always copied, the second column copied is the one that varies. I'm thinking something like this inside the loop
Sheets(1).Activate
Range("A1:A14").Select
'This is where I need to copy the next column over and increment every time the code loops
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Help would be appreciated. Thank You.
Your best bet is to use Cells() inside of your Range() in order to loop through each column. This bit of code should help you out:
Sub columnCopy()
Dim sh1 As Worksheet
Set sh1 = sheets("Sheet1")
lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column ' Last Column
For i = 2 To lc
sh1.Range(sh1.Cells(1, 1), sh1.Cells(12, 1)).Copy
Worksheets.Add
sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteValues
sh1.Range(sh1.Cells(1, i), sh1.Cells(12, i)).Copy
sheets(1).Cells(1, 2).PasteSpecial Paste:=xlPasteValues
Next i
End Sub
这篇关于VBA:循环从主页复制某些列,创建一个新的工作表并粘贴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!