PDA

View Full Version : Need to specify columns to be copied in this VBA code



alaney
09-21-2015, 09:01 AM
Hello,

I have this great VBA code that I found that copies and formats several worksheets into one worksheet. The problem is that I don't want all the columns put in the summary, just Columns A:M. Can some please help with how I get this code to only combine the data in Columns A:M into the new "target" worksheet. Thank you in advance.



Sub CombineSheets()
'This macro will copy all rows from the first sheet
'(including headers)
'and on the next sheets will copy only the data
'(starting on row 2)

Dim i As Integer
Dim j As Long
Dim SheetCnt As Integer
Dim lstRow1 As Long
Dim lstRow2 As Long
Dim lstCol As Integer
Dim ws1 As Worksheet

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

On Error Resume Next

'Delete the Target Sheet on the document (in case it exists)
Sheets("Target").Delete
'Count the number of sheets on the Workbook
SheetCnt = Worksheets.Count

'Add the Target Sheet
Sheets.Add after:=Worksheets(SheetCnt)
ActiveSheet.Name = "Target"
Set ws1 = Sheets("Target")
lstRow2 = 1
'Define the row where to start copying
'(first sheet will be row 1 to include headers)
j = 1

'Combine the sheets
For i = 1 To (SheetCnt - 4)
Worksheets(i).Select

'check what is the last column with data
lstCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(x1ToLeft).Column

'check what is the last row with data
lstRow1 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

'Define the range to copy
Range("A" & j, Cells(lstRow1, lstCol)).Select

'Copy the data
Selection.Copy
ws1.Range("A" & lstRow2).PasteSpecial
Application.CutCopyMode = False

'Define the new last row on the Target sheet
lstRow2 = ws1.Cells(65536, "A").End(xlUp).Row + 1

'Define the row where to start copying
'(2nd sheet onwards will be row 2 to only get data)
j = 2
Next

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Sheets("Target").Select
Cells.EntireColumn.AutoFit
Range("A1").Select

End Sub

SamT
09-21-2015, 04:18 PM
I put your code inside CODE tags to format it. Use the # icon the the editor menu.


I don't want all the columns put in the summary, just Columns A:M. Can some please help with how I get this code to only combine the data in Columns A:M


'check what is the last column with data
lstCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(x1ToLeft).Column

Hint: LstCol is a mnemonic for "Last Column." The column number of Column "M": is 13.