Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check and skip column during paste
Hi,
I have this macro to copy a row of data from shtB(bkData) to shtA(bkMaster), if we find the part number we want shtB. This is fairly straight forward. If Trim(shtB.Cells(irow2, 1)) = PartNo Then Fnd = True shtB.Cells(irow2, 2).Resize(1, 12).Copy bkMaster.ActiveSheet.Cells(irow + 2, 3).PasteSpecial Paste:=xlPasteValues End If The original format in Excel file looks like this: -------------------------------------------------- A B C D E F G H I J K PartNo wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12 New column added in between work weeks: --------------------------------------- A B C D E F G H I J K ... PartNo wk3 wk4 Total wk5 wk6 wk7 wk8 Total wk9 wk10 ... Now the bkMaster has added a few new columns in between. The column heading is named "Total". What I would like the program to do is that before the .pastespecial command, the code will check the heading in Row1. So that data from ShtB will be copied to the correct column in shtA. If heading = "wk" & some numbers, then paste the data. If heading < start with "wk", then it will skip/jump to the next column, check the heading again, if heading = "wk" & some numbers, then continue to paste the next piece of data. I don't know how do this in proper VBA code. Can someone help, please? Thank you in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check and skip column during paste
Try the below
Dim lngCol As Long, intCount As Integer If Trim(shtB.Cells(irow2, 1)) = PartNo Then Fnd = True lngCol = 2 Do If bkmaster.ActiveSheet.Cells(1, lngCol) Like "wk*" Then intCount = intCount + 1 bkmaster.ActiveSheet.Cells(irow + 2, lngCol) = _ shtB.Cells(irow2, 2).Resize(1, 12).Cells(intCount) End If lngCol = lngCol + 1 Loop Until bkmaster.ActiveSheet.Cells(1, lngCol) = "" End If If this post helps click Yes --------------- Jacob Skaria "choo" wrote: Hi, I have this macro to copy a row of data from shtB(bkData) to shtA(bkMaster), if we find the part number we want shtB. This is fairly straight forward. If Trim(shtB.Cells(irow2, 1)) = PartNo Then Fnd = True shtB.Cells(irow2, 2).Resize(1, 12).Copy bkMaster.ActiveSheet.Cells(irow + 2, 3).PasteSpecial Paste:=xlPasteValues End If The original format in Excel file looks like this: -------------------------------------------------- A B C D E F G H I J K PartNo wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12 New column added in between work weeks: --------------------------------------- A B C D E F G H I J K ... PartNo wk3 wk4 Total wk5 wk6 wk7 wk8 Total wk9 wk10 ... Now the bkMaster has added a few new columns in between. The column heading is named "Total". What I would like the program to do is that before the .pastespecial command, the code will check the heading in Row1. So that data from ShtB will be copied to the correct column in shtA. If heading = "wk" & some numbers, then paste the data. If heading < start with "wk", then it will skip/jump to the next column, check the heading again, if heading = "wk" & some numbers, then continue to paste the next piece of data. I don't know how do this in proper VBA code. Can someone help, please? Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO PASTE CELL to COLUMN, SKIP ROWS | Excel Discussion (Misc queries) | |||
VSTO install package-How to check for prerequisites and skip them | Excel Programming | |||
paste formula and skip blanks | Excel Discussion (Misc queries) | |||
copy paste formula to skip rows | Excel Worksheet Functions | |||
Paste Special - Skip Blanks | Excel Discussion (Misc queries) |